Highlight multiple cells (one column) that are less than today's date

Hello,

I have a column of dates that needs to be compared current date and
highlighted if the date is less that today's date.


The dates were populated in a general non-specific format (below) and vary
in length. My plan was to create a macro, that uses conditional formatting
to say; if the cell date is less than today's date highlight it yellow. I
am not finding this to be an easy task! Anyhow, after creating the
conditional formatting; I apply it the column by using a past special, then
lastly apply a format to the entire column of dates as DATE "*3/14/2001".
What happens really doesn't work like expected. The date reverses itself in
the format of 05/31/2006 without applying any highlight for the conditional
format. Does anyone know how, I can get this to work? I don't mind the
date being reversed, but it's not being recognized or highlighted from the
conditional formatting. Help!



The column below should have two dates highlighted in yellow (second and
last).





2006/05/31



2004/09/30



2005/07/08



2004/07/31

I am not sure if this is the best solution, but I have found that
=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2)) seem to work and recognizes the
conditional formating. However, I am not sure how to apply this formula to
the same column of general non-specific text dates.

Can anyone help?

"bgreen" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a column of dates that needs to be compared current date and
> highlighted if the date is less that today's date.
>
>
> The dates were populated in a general non-specific format (below) and vary
> in length. My plan was to create a macro, that uses conditional
formatting
> to say; if the cell date is less than today's date highlight it yellow. I
> am not finding this to be an easy task! Anyhow, after creating the
> conditional formatting; I apply it the column by using a past special,
then
> lastly apply a format to the entire column of dates as DATE "*3/14/2001".
> What happens really doesn't work like expected. The date reverses itself
in
> the format of 05/31/2006 without applying any highlight for the
conditional
> format. Does anyone know how, I can get this to work? I don't mind the
> date being reversed, but it's not being recognized or highlighted from the
> conditional formatting. Help!
>
>
>
> The column below should have two dates highlighted in yellow (second and
> last).
>
>
>
>
>
> 2006/05/31
>
>
>
> 2004/09/30
>
>
>
> 2005/07/08
>
>
>
> 2004/07/31
>
>
> .
>
>
>
>
>
>
> Kind regards,
>
>
>
> - Bernie
>
>

I'm unable to find the original message, so I try a reply on this one.
I hope the answer will get to the right person.

It seems that the ISO8601 standard is used for the dates (International
Format).
So, the cells containing this dates should be formatted as Custom with as
type "yyyy-mm-dd"
Afterwards, a simple Conditional Formatting will do the trick :
Select the Date Cells and select Formula "=A1<TODAY()"
and of couse the format you want (e.g. Patterns -> Yellow)

Hope this helps,
Bruno



"bgreen" <[email protected]> wrote in message
news:%23%[email protected]...
> I am not sure if this is the best solution, but I have found that
> =DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2)) seem to work and recognizes the
> conditional formating. However, I am not sure how to apply this formula
to
> the same column of general non-specific text dates.
>
> Can anyone help?
>
> "bgreen" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have a column of dates that needs to be compared current date and
> > highlighted if the date is less that today's date.
> >
> >
> > The dates were populated in a general non-specific format (below) and
vary
> > in length. My plan was to create a macro, that uses conditional
> formatting
> > to say; if the cell date is less than today's date highlight it yellow.
I
> > am not finding this to be an easy task! Anyhow, after creating the
> > conditional formatting; I apply it the column by using a past special,
> then
> > lastly apply a format to the entire column of dates as DATE
"*3/14/2001".
> > What happens really doesn't work like expected. The date reverses
itself
> in
> > the format of 05/31/2006 without applying any highlight for the
> conditional
> > format. Does anyone know how, I can get this to work? I don't mind the
> > date being reversed, but it's not being recognized or highlighted from
the
> > conditional formatting. Help!
> >
> >
> >
> > The column below should have two dates highlighted in yellow (second and
> > last).
> >
> >
> >
> >
> >
> > 2006/05/31
> >
> >
> >
> > 2004/09/30
> >
> >
> >
> > 2005/07/08
> >
> >
> >
> > 2004/07/31
> >
> >
> > .
> >
> >
> >
> >
> >
> >
> > Kind regards,
> >
> >
> >
> > - Bernie
> >
> >
>
>