Conditional Formatting for Due Dates

[thoma011]

I have seen similar posts in the forum for similar issues, but none of the ideas seem to work for my purposes. I am using Excel 2003, and need to find conditional formating to alert clinicians when due dates are approaching, then turn off the formatting when a completion date is entered in another cell.


E2: Report Due Date

F2: Completion date


I want E2 to turn green when due date is within 30 days of today. Amber when due date is within 10 days of today, and red when overdue. Cell color will revert to normal when completion date is entered into F2.


I have tried the following functions based on suggestions here and in other forums.


=AND(ISBLANK(F2),TODAY()-30>E2) -Didn't work


=AND(E2>(TODAY()-30), F2<>"") -Worked, but in reverse. Color changed only when completion date was entered in F2


Any other thoughts? Thanks in advance!

 

[SirJB7]

Hi, thoma011!

Try assigning 3 CF to column E with this formula, changing XXX by 0, 10 & 30, in that order:

=Y(ESNUMERO(E1);ESBLANCO(F1);HOY()+XXX>E1) -----> in english: =AND(ISNUMBER(E1),ISBLANK(F1),TODAY()+XXX>E1)

Set background to red, yellow and green, respectively.

Regards!