I have a column with cells in the format yyyy-mm-dd hh:mm:ss which I suspect is actual text, not a 'real' time, because formatting the cell as just the date 2001-03-21 does nothing.
I want to get rid of the blank and the time part, leaving just yyyy-mm-dd.
Is there any easy way of substringing each cell "in situ" to leave just the left 10 characters, or do I need to set up another column, at least temporarily?
Thanks!
Removing time from after date
-
- PlatinumLounger
- Posts: 5411
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Removing time from after date
John Gray
"(or one of the team)" - how your hospital appointment letter indicates that you won't be seeing the Consultant...
"(or one of the team)" - how your hospital appointment letter indicates that you won't be seeing the Consultant...
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing time from after date
Select the relevant cells.
Press Ctrl+H to activate the Replace dialog.
Enter a space followed by an asterisk in the Find what box.
Leave the Replace with box blank.
Click Replace All.
(Make sure that the find/replace action is not set to match entire cell contents)
Press Ctrl+H to activate the Replace dialog.
Enter a space followed by an asterisk in the Find what box.
Leave the Replace with box blank.
Click Replace All.
(Make sure that the find/replace action is not set to match entire cell contents)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 5411
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Re: Removing time from after date
Fantastic, Hans! Just a matter of then formatting the column cells with Date yyyy-mm-dd format, since the cells had changed to dd/mm/yyyy format.
(Can you become an MMVP?!)
(Can you become an MMVP?!)
John Gray
"(or one of the team)" - how your hospital appointment letter indicates that you won't be seeing the Consultant...
"(or one of the team)" - how your hospital appointment letter indicates that you won't be seeing the Consultant...
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 5411
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Re: Removing time from after date
Marvellous MVP!
John Gray
"(or one of the team)" - how your hospital appointment letter indicates that you won't be seeing the Consultant...
"(or one of the team)" - how your hospital appointment letter indicates that you won't be seeing the Consultant...
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing time from after date
OK, thanks - it could as well have been Mediocre MVP, Mindless MVP, Medieval MVP, Marylebone MVP, ...
Best wishes,
Hans
Hans