Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reformat a date imported from another program
I export data from another program into Excel. This data contains dates
which are formatted like this: yyyymmdd000000. When the data is imported into Excel, the date shows in the cell like #.#####E+13. The formula bar shows the date as it appears in the original program (yyyymmdd000000). How can this date format be converted to mm/dd/yyyy or another date format which excludes extraneous zeros? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reformat a date imported from another program
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
-- ** John C ** "Heather" wrote: I export data from another program into Excel. This data contains dates which are formatted like this: yyyymmdd000000. When the data is imported into Excel, the date shows in the cell like #.#####E+13. The formula bar shows the date as it appears in the original program (yyyymmdd000000). How can this date format be converted to mm/dd/yyyy or another date format which excludes extraneous zeros? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reformat a date imported from another program
Problem is the data are imported as numbers. Since the format has 14
digits no information is lost. I don;t know of a format to hide least significant digits of integers but you can construct a date in another column with the following formula (assume first date in A2) =DATE(--LEFT(A2,4),--MID(A2,5,2),--MID(A2,7,2)) Note that you can ensure that the full imported string will be displayed with either of the following Number Formats for the dates column: 1. You can format the column as Text. 2. You can apply a custom Number Format of "00000000000000" (i.e. 14 zeros) HTH Kostis Vezerides On Nov 13, 8:53*pm, Heather wrote: I export data from another program into Excel. *This data contains dates which are formatted like this: yyyymmdd000000. *When the data is imported into Excel, the date shows in the cell like #.#####E+13. *The formula bar shows the date as it appears in the original program (yyyymmdd000000). *How can this date format be converted to mm/dd/yyyy or another date format which excludes extraneous zeros? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reformat a date imported from another program
Another one:
=--TEXT(LEFT(A1,8),"0000\-00\-00") And format the cell as a date. =text() will return a string. The -- stuff converts that text date to a real number. Reformatting makes it look pretty. Heather wrote: I export data from another program into Excel. This data contains dates which are formatted like this: yyyymmdd000000. When the data is imported into Excel, the date shows in the cell like #.#####E+13. The formula bar shows the date as it appears in the original program (yyyymmdd000000). How can this date format be converted to mm/dd/yyyy or another date format which excludes extraneous zeros? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reformat a 6-digit date to general & look the same? | Excel Discussion (Misc queries) | |||
Copy pasting and reformat Date | Excel Discussion (Misc queries) | |||
reformat cells for date | Excel Discussion (Misc queries) | |||
Imported Date Format | New Users to Excel | |||
Date from imported file | Excel Discussion (Misc queries) |