ExcelBanter - Convert Text to DATE

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Convert Text to DATE (https://www.excelbanter.com/excel-discussion-misc-queries/34214-convert-text-date.html)

Dimmer

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

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

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

"Dimmer" wrote in message
...
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 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
"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

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

"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

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

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

"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

Bernard Liengme

Zuj

Covert (1/1,F,0) to (1/1) help need

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

"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

David Biddulph[_2_]

Covert (1/1,F,0) to (1/1) help need

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).
--
David Biddulph

"Zuj" wrote in message
...
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

"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

All times are GMT +1. The time now is 11:18 PM.