format number to date

[Picodillo]

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.

 

[T. Valko]

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

 

[Jacob Skaria]

Please could you clarify how the date would read. You have mentioned this as
1090720

If you mean to say it looks as 090720 in text format; try the below formula
=DATE(2000+LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

If this post helps click Yes

 

[David Biddulph]

Try forcing it to a date by changing the
=B1&"/"&C1&"/20"&"A1
to
=--(B1&"/"&C1&"/20"&"A1)
which should work if your Windows Regional Settings (in Control Panel) are
looking for MDY format.

 

[T. Valko]

=--(B1&"/"&C1&"/20"&"A1)

Think there's an extraneous " in there.

=--(B1&"/"&C1&"/20"&A1)

 

[Jacob Skaria]

Extra " from the formula OP posted..

If this post helps click Yes

 

[Rick Rothstein]

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 Rothstein]

...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.

 

[T. Valko]

I have no experience with international issues

Neither do I so I don't take that into consideration when I make a
suggestion. If something I suggest is not "internationally compatible" I
assume the OP will feedback and then we'll go from there.