Date Time Format

  • Thread starter Thread starter Toria
  • Start date Start date
T

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 ##############.
 
Hi,

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

200811

Mike
 
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
 
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.
 
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!
 
Back
Top