PDA

View Full Version : "yyyymmdd_hhmm" change to Date, Time format



Ann_BBO
04-22-2009, 06:42 PM
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
04-22-2009, 07:45 PM
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
04-22-2009, 09:13 PM
Thanks. It works.

JONvdHeyden
04-23-2009, 12:03 AM
Hello Ann_BBO

Another, and perhaps simpler way:

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

And format as date and time.

xluser2007
04-23-2009, 01:50 AM
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
04-23-2009, 02:02 AM
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
04-23-2009, 02:14 AM
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
04-23-2009, 02:31 AM
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
04-23-2009, 03:02 AM
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
04-23-2009, 04:04 AM
Thanks all help~~xluser & Jon