Conditional formatting of dates

Hi there,

I really hope that this is not a repeat of anything already answered a dozen times, but I've genuinely looked on-line for sometime and cannot seem to see a definitive answer to my query regarding colour conditional formatting of dates to reflect the following:

1. If date in cell is less than today then text is RED.
2. If date in cell is equal to or greater than today, but less than 5 days in future, then text is AMBER.
3. If date in cell is equal to or greater than 5 days from today, then text is GREEN

I can usually do one or two of the above, but never seem to get all 3 to work.

Any help would be appreciated.

See http://www.xldynamic.com/source/xld.CF.html#due

Hi Bob,

Thanks for the link. At first glance it looks perfect, but unfortunately it does not appear to be working on my Excel 2010 sheet.

When I use the formulas mentioned, it seems to work for the dates that are less than today, but the remaining formulas either do nothing or change all entries.

It also does not dynamically update the conditionally formatted cells if I were to input a date less than today. If I were to choose to change the colour fill for a cell, then it changes more cells than just where the date is less than today. For example, blank cells.

I'll keep looking...thanks again though! )

Check that the formulas you entered don't have quotes around them. That is a common error.

No sign of any quotes...just rechecked.

Can you post a sample workbook showing this?

Thanks for requesting example workbook for further review etc...I've cut down my requirements and can now use:

Conditional Formatting / Highlight Cells Rules / Less than =Today()
Conditional Formatting / Highlight Cells Rules / A Date Occurring "Next Week"

This should be sufficient....

Thanks again...this is a great site! :thumb: