Convert Text to DATE

D

Dimmer

Hello all,

I hope you can help with a problem we have in the office. We've got over
4,500 observations which are supposed to be in Date Format. Instead they are
in text format, e.g. "01.06.2005 08:26" or just "09.03.2005" but we need to
perform various duration calculations on them, so we need them in the Date
Format

I tried Format Cells-> Category = Date, but it doesn't work. It works
however when I enter the cell and then click "Enter". very strange

Please help if you have an idea how to do it...

Thank you very much
 
B

Bernard Liengme

Try =DATEVALUE(SUBSTITUTE(A1,".","/")); this worked for me, I had to format
the cell to data otherwise it displayed the date serial number.
If your system is set to use dots for dates try =DATEVALUE(A1)
best wishes
 
D

Dave Ramage

Dimmer,

You can use this formula:
=DATEVALUE(SUBSTITUTE(E9,".","/"))+IF(LEN(E9)>10,TIMEVALUE(RIGHT(A1,5)))

Then copy and Paste Special- Values to get back to numbers rather than
formula.
Cheers,
Dave
 
D

Dimmer

Guys thank you very much, both suggestion worked! However, we just discovered
we have another problem - some dates are in the American Format (June 1st =
06.01) and the others in the European one (01.06)...
Any ideas how can I covert them all into European format? (just changing the
date format doesn't do it. for example if it's June 1st american - 06.01 -
then when changing to European date format we still have 06.01 or the 6th of
January)

Thanks again
 
D

Dave Peterson

Slight typo:
=DATEVALUE(SUBSTITUTE(E9,".","/"))+IF(LEN(E9)>10,TIMEVALUE(RIGHT(E9,5)))
or
=DATEVALUE(SUBSTITUTE(a1,".","/"))+IF(LEN(a1)>10,TIMEVALUE(RIGHT(A1,5)))

(mixture of A1 and E9's)

And remember to format the cell as a nice date/time.
 
J

John

Hi,

Try a custom format instead.
Type in the format you require.
Otherwise we used a formula to get it into the format we needed.
Pull in the 1st 2 characters+/+middle two characters+/+last two from the cell.

i.e. =(left(a2,2)&"/"&mid("a2,4,2")&"/"&right(a2,2)
Not perfect on middle part but play around with it.

Hope this helps
 
B

Bernard Liengme

Try =DATEVALUE(SUBSTITUTE(A1,".","/")); this worked for me, I had to format
the cell to data otherwise it displayed the date serial number.
If your system is set to use dots for dates try =DATEVALUE(A1)
best wishes
 
Z

Zuj

Dear all,

i m facing a problem with data sorting. my data is show as (1/1,F,0),
however i require only the number vaule in this field. i want to convert
1/1,F,0 into 1/1. tell me how can it be done???..

example:

1/1,F,0 => 1/1
1/1,OK,0 => 1/1
1/1,OK,0 => 1/1
1/1,OK,0 => 1/1
 
D

David Biddulph

Data, text to columns, delimited by comma, skip the last 2 columns (and tell
it to treat the first column as text, or else it will read the 1/1 as
01-Jan).
 
Top