Removing Dates

B

Brento

HI All,

Hopefully a quick one. I have a serious of info in a cell in this
format

11/08/2006 12:00:00
10/08/2006 12:15:00

I need to keep the time but remove all the dates from the cell. I am
using the replace function however the dates can only be run 1 day at a
time and I have over a years worth to remove,

Is there a generic format ie **/**/**** That will when put in the
replace bar will remove all dates no matter when they are?

Thanks in advance
Brento
 
D

David Biddulph

Brento said:
HI All,

Hopefully a quick one. I have a serious of info in a cell in this
format

11/08/2006 12:00:00
10/08/2006 12:15:00

I need to keep the time but remove all the dates from the cell. I am
using the replace function however the dates can only be run 1 day at a
time and I have over a years worth to remove,

Is there a generic format ie **/**/**** That will when put in the
replace bar will remove all dates no matter when they are?
Click to expand...

If the data is entered as a date & time, as distinct from as text, then you
can just reformat the cells.

If it is text, you may be able to split it using Data/ text to columns
(using the space as a delimiter), or more sensibly you may be able to use
the same function to convert the whole cell from text to date & time
(choosing an appropriate date format at the last stage of the wizard), then
reformat accordingly.
 
A

Arjuna

hi,
use following function if your data is in text format:

=+TIME(MID(TRIM(MID(B15,SEARCH("
",B15,1)+1,LEN(B15))),1,2),MID(TRIM(MID(B15,SEARCH("
",B15,1)+1,LEN(B15))),4,2),MID(TRIM(MID(B15,SEARCH("
",B15,1)+1,LEN(B15))),7,2))

where B15 is cell containing your datetime val.
 
G

Gord Dibben

I tried Data>Text to Columns on your example dates/times and was sucessful in
splitting the time off from the date.

Fixed width.

Clear the break line between 12:15 & PM and don't import the date column.


Gord Dibben MS Excel MVP
 
Top