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

"Toria" wrote:

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

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
--
Gary''s Student - gsnu200821


"Toria" wrote:

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

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

"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200821


"Toria" wrote:

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

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" wrote in message
...
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 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!

"David Biddulph" wrote:

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

"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200821


"Toria" wrote:

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