Date Time Format

[Toria]

Hello,

I have a number that looks like this: 2.01E+13. This is formatted as
scientific in Format Cells. This actual number is 20081100000000. This needs
to be in a date/time format, but Format Cells just gives ##############.

 

[Mike H]

Hi,

For that string what would you like the date to be and why, all I see are
the numbers?

200811

Mike

 

[Gary''s Student]

Say A1 contains:
20081218133512
then enter:
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

to display 12/18/2008 13:35:12

to get this use Format > Cells... > Number > Custom > m/dd/yyyy hh:mm:ss

 

[David Biddulph]

Or =--TEXT(A1,"0000\-00\-00 00\:00\:00"), and format the cell to suit.

 

[Fred Smith]

The problem I see is that 20081100000000 is not a proper date in any format.
The day of the month can't be zero. The other recommendations will work, as
long as you have a proper date which can be interpreted by their formulas.

Regards,
Fred.

 

[Toria]

Hi David,

Thanks for this formula! This is great. Like Fred and Mike said, I need to
get the true date, but once I do, this will work. Thanks again!