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

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!

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

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

Try formatting the cell as text?


On Aug 2, 11:52 am, wrote:
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!

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

=--(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)
--
David Biddulph

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

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

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

On Aug 2, 4:52 pm, wrote:
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!

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

Try this:

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

Format cells as : date


" wrote:

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!