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?

=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?

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?

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