Excel is seeing the five-digit number as a Julien date.
You'll have this problem with any date entries that don't
include the usual delimiters (slashes or dashes). The
best options I can suggest are to convert the dates to
Julien dates before pasting them in, or add the delimiters
using a macro.
If the dates have a consistent number of digits this will
be easy:
-parse the number out into separate columns for month,
day, and year
-concatenate them back together with the delimiters (the
date now looks right, but it is a text value)
-copy the concatenation result and paste-special as values
(they will still be text)
-Now the annoying part: you have to "touch" each cell to
get Excel to recognize it as something other than text. I
just click F2-Enter over and over until I've fixed the
whole column. Now you have dates that can be formatted
any way you want, and Excel will paste them as Julien
dates into new cells.
If leading zeros were not used to create dates with a
uniform number of digits, this will be more challenging,
but not impossible. The dates would have to be sorted and
parsed according to some knowlege of the dates - four- and
six-digit dates would be easy - MDYY and MMDDYY. Five-
digits are harder, could be MDDYY or MMDYY, and these may
be ambiguous even to the human user. Hopefully the file
has consistency in two- or four-digit year entries.
I'd suggest to the programmer of the source file that the
date inputs be standardized for easier conversion in the
future, as well.
Good Luck!
