| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looking for a production schedule excel/access & user friendly? | Setting up and Configuration of Excel | |||
| Drill to Details - is there user friendly way to delete the sheet | Excel Worksheet Functions | |||
| entering data using - between numbers without converting to date | Excel Discussion (Misc queries) | |||
| Bar chart No printer friendly. | Charts and Charting in Excel | |||
| how to get hyperlink from the friendly name in xl. | Excel Discussion (Misc queries) | |||
Hybrid Mode
