Date & Time Conversion from xml import

L

loza

Saved my mobile phone msgs as xml doc When I open it with excel 2003 Date and
Time is displayed as

A1 Date/Time
A2 2.00806E+13
Formula Bar displays 20080627155119

How do I format to display as 26 Jun 08 3.35pm as per phone details or
simular date and time format

Regards Loza
 
R

Roger Govier

Hi Loza

Try
=MID(B2,7,2)&" "&TEXT(MID(B2,5,2),"mmm")&" "&LEFT(B2,4)&"
"&MID(B2,9,2)&":"&MID(B2,11,2)+0
 
D

David Biddulph

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))
and format as dd mmm yy h:mm AM/PM or as you wish.

It doesn't give the answer you gave in your example, of course, but I am
assuming that the input data is in the form yyyymmddhhmmss.
 
Top