Convert Text to DATE

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

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dimmer" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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
"Dimmer" wrote:

> 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
>
>

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

"Dave Ramage" wrote:

> 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
> "Dimmer" wrote:
>
> > 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
> >
> >

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.


Dave Ramage wrote:
>
> 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
> "Dimmer" wrote:
>
> > 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
> >
> >

--

Dave Peterson

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

"Dimmer" wrote:

> 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
>
>

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dimmer" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>