...wouldn't the interpretation of month, day and date be
regionally (locale) sensitive for a 2-digit year?

To be clear on the above question... I am not sure if this is the case or
not (I have no experience with international issues); it just seems that
whenever a decision regarding interpretation of ambiguous information is
left up to Excel to sort out, it tends to rely on the computer's regional
settings for its interpretation.

As for the "what does the 1 in front of the number mean" point I raised...
the OP (if he is reading this far down in the thread) could resolve the
issue for us by posting the number his query returns for a date prior to the
year 2000.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I wonder whether that "1" in the front is meant to be a century correction
factor... 0 for 19xx and 1 for 20xx (the one being added to the 19 to get
the 20)?

I'm also a little hesitant about your formula.... wouldn't the
interpretation of month, day and date be regionally (locale) sensitive for
a 2-digit year? Now, if the year part were 4 digits long, then you could
use dash delimiters and that would put the value into an international
date format. Assuming the 1 is meant to be interpreted as I did above,
then this would work..

=--TEXT(A1+19000000,"0000-00-00")

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
1090720

If these are a consistent format where the actual date starts at the 2nd
digit and the year is *always* 20xx:

=--TEXT(20&MID(A1,2,6),"0000\/00\/00")

Format as Date

--
Biff
Microsoft Excel MVP


"Picodillo" wrote in message
...
When I run a query from work and post the information in Excel, the date
shows an odd format. For instance, for July 20, 2009, the date would
read
1090720. I would like to format this into a date that is recognized by
Excel. I am able to spread the data out using "text to columns" and
then
using =B1&"/"&C1&"/20"&"A1 so that it looks like 7/20/2009, but Excel
doesn't
recognize this as a date so I can't do any conditional formatting.
Please
help.