Converting data download for date 2.00707E+11 into something more user-friendly

[data08]

Received a data download into Excel and noted that the date is listed
as "2.00707E+11" [which is basically 200707011300 and includes
YYYYMMDDHHMM]. I tried converting it to other date formats but got
this
"###############################################################################################################################################################################################################################################################"
instead. Please HELP. Thanks in advance!

 

[Dave F]

No, 2.00707E+11 is 2.00707 * 10^11; this is how Excel displays
scientific notation.

Try formatting the cell as text?

 

[David Biddulph]

=--(MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)&"
"&MID(A1,9,2)&":"&MID(A1,11,2)) or
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),0)

 

[Pete_UK]

Assuming your "date" is in A1, use this formula to split it up:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))
+TIME(MID(A1,9,2),MID(A1,11,2),0)

Format the cell with the formula in as dd/mm/yyyy hh:mm.

If you want the date and time in separate columns, you can do this:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

=TIME(MID(A1,9,2),MID(A1,11,2),0)

Format the cells appropriately.

Hope this helps.

Pete

 

[Guest]

Try this:

=--TEXT(A1,"0000\/00\/00 00\:00")

Format cells as : date