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? |
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? |
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? |
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 |
All times are GMT +1. The time now is 08:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com