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

[bgreen]

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

 

[bgreen]

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?

 

[Bruno Vermeersch]

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