Excel 2007 Conditional Formatting Questiond

May 13, 2011 at 09:47:01
Specs: Windows 7
I have a spreadsheet that lists dates for individuals having last completed an interview.

a2=date interview last complete
a3=formula: =DATE(YEAR(A2)+10,MONTH(A2),DAY(A2)) This figures the last interview complete day, plus 10 years which equals when they need to submit their paperwork for their next interview.

What i would like to do is conditionally format A3 so that:
If their due date is more than 2 months out it is green
If their due date is within 2 months they turn yellow
if their due date has passed it turns red.

A2= 01/01/2011 A3= 01/01/2021 Cell= Green
A2= 08/12/2001 A3= 06/12/2011 Cell= Yellow (Due within 60 days from today)
A2= 05/04/2001 A3= 03/04/2011 Cell= Red (Due date has passed today)

I hope all this makes sense.

See More: Excel 2007 Conditional Formatting Questiond

Report •

May 14, 2011 at 08:02:51
Try this:

For 30 days: =IF((TODAY()-30)<=$A3,TRUE,FALSE) Red
For 60 days: =IF((TODAY()-60)<=$A3,TRUE,FALSE) Yellow
For 90 days: =IF((TODAY()-90)<=$A3,TRUE,FALSE) Green

Modify the days to suit your needs.

In the Conditional Formatting window, it must be in the order shown above,
first red (top of list), then yellow, then green (bottom of list).



Report •
Related Solutions

Ask Question