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.

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.

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.

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

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.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
...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.

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
---------------
Jacob Skaria


"Picodillo" wrote:

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.

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

Picodillo wrote:
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.

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

Think there's an extraneous " in there.

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

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

Picodillo wrote:
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.

Extra " from the formula OP posted..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

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

Think there's an extraneous " in there.

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

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

Picodillo wrote:
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.