"yyyymmdd_hhmm" change to Date, Time format

[Ann_BBO]

Hi All,

I have a question in excel time format.
If "A1" = 20090416_1454,
then how to change to "B1" = 4/15/09 14:54

I know how to change the yyyymmdd format to mm/dd/yy, but i don't know how to change time.

Thanks
Ann

[xluser2007]

Hi All,

I have a question in excel time format.
If "A1" = 20090416_1454,
then how to change to "B1" = 4/15/09 14:54

I know how to change the yyyymmdd format to mm/dd/yy, but i don't know how to change time.

Thanks
Ann

In B2 try:


=DATE(--LEFT(A1,4),--MID(LEFT(A1,8),5,2),--RIGHT(LEFT(A1,8),2))+TIMEVALUE(LEFT(RIGHT(A1,4),2)&":"&RIGHT(RIGHT(A1,4),2))

and format cell as:


mm/dd/yy hh:mm

[Ann_BBO]

Thanks. It works.

[JONvdHeyden]

Hello Ann_BBO

Another, and perhaps simpler way:

=TEXT(SUBSTITUTE(A1,"_",""),"00\/00\/00 00\:00")+0

And format as date and time.

[xluser2007]

Hello Ann_BBO

Another, and perhaps simpler way:

=TEXT(SUBSTITUTE(A1,"_",""),"00\/00\/00 00\:00")+0

And format as date and time.

Hi Jon, great solution, I knew there would a be a simpler approach.

Could you please explain how the "\" work in the conditional format, haven't used them before?

regards

[JONvdHeyden]

Hello

I think it's referred to as a 'break character'. So we're effectively splitting the text string there, and then inserting the desired character. The when the text string format resembles date and time we coerce it to a value and reformat it to a date and time format (using +0).

[xluser2007]

Hello

I think it's referred to as a 'break character'. So we're effectively splitting the text string there, and then inserting the desired character. The when the text string format resembles date and time we coerce it to a value and reformat it to a date and time format (using +0).

Thanks Jon, always nice to learn new tricks.

One more question though. I don't quite understand how it understood the space between the section "/00 00\:00"". That is how it intutively split out that last time component after the space, i would have thought it needed another 'break character'. I tried


"=TEXT(SUBSTITUTE(A1,"_",""),"00\/00\/00\00\:00")+0

for example, but that gave a #VALUE! error. So a bit unsure how it knew to split that part up.

[JONvdHeyden]

If you look at date and time, e.g.

15/05/2009 09:05

If you interpret it as a text string then you see there is a space between the date and time component.

so:

15052009_0905

First we substitute the underscore with nothing so it becomes:

150520090905

Then we apply the format:
00\/00\/0000 00\:00

Note in the format above the inclusion of a space between the date and time components. This will make it read exactly the same as the date (as a string) that we started off with. You cannot place another break there because otherwise it will read as follows:

15/05/200909:05

You basically just missing the space, make sense?

[xluser2007]

Thanks Jon, that clarifies it :thumb.

I forgot that we are coercing to a text that can be formatted into the " mm/dd/yy hh:mm" format afterwards.

cheers,

[Ann_BBO]

Thanks all help~~xluser & Jon