Format serial date and time

[Cedric]

I have been trying to format a serial date to the format yyyy/mm/dd 00;00 am
An example of the text is 200501090932. The time is in military time. I have
tried the text ot columns but my string is too long. Any help will be greatly
appreciated.

[Barb Reinhardt]

The serial date in EXCEL for the string you gave is 38361.40.

I converted it using the following equation
=DATE(LEFT(A1,4),MONTH(MID(A1,5,2)),DAY(MID(A1,7,2)))+TIME(MID(A1,9,2),MID(A1,11,2),)

where A1 contained the value 200501090932.



"Cedric" <Cedric@discussions.microsoft.com> wrote in message
news:FF50FE3A-17F7-4DE4-82EA-F4FD30B3EC0F@microsoft.com...
>I have been trying to format a serial date to the format yyyy/mm/dd 00;00
>am
> An example of the text is 200501090932. The time is in military time. I
> have
> tried the text ot columns but my string is too long. Any help will be
> greatly
> appreciated.

[Bob Phillips]

Another way

=--TEXT(TEXT(LEFT(A1,8),"0000\-00\-00"),"dd/mm/yyyy")--TEXT(TEXT(RIGHT(A1,4)
,"00\:00"),"hh:mm")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barb Reinhardt" <reply@tonewsgroup.com> wrote in message
news:uETU7ylCGHA.744@TK2MSFTNGP10.phx.gbl...
> The serial date in EXCEL for the string you gave is 38361.40.
>
> I converted it using the following equation
>
=DATE(LEFT(A1,4),MONTH(MID(A1,5,2)),DAY(MID(A1,7,2)))+TIME(MID(A1,9,2),MID(A
1,11,2),)
>
> where A1 contained the value 200501090932.
>
>
>
> "Cedric" <Cedric@discussions.microsoft.com> wrote in message
> news:FF50FE3A-17F7-4DE4-82EA-F4FD30B3EC0F@microsoft.com...
> >I have been trying to format a serial date to the format yyyy/mm/dd 00;00
> >am
> > An example of the text is 200501090932. The time is in military time. I
> > have
> > tried the text ot columns but my string is too long. Any help will be
> > greatly
> > appreciated.
>
>