MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional dates

Posted by Chris on July 21, 2001 9:51 PM

I have two dates, administered and due. If the date is equal or greater than the due date I want it red. If the date is 30 days before due then yellow. And if further out than 30 green. I have tried

=$G$9=TODAY()-1 variants but cant figure it out, help!!

Posted by Aladin Akyurek on July 21, 2001 11:15 PM

I'll assume that you want to color cells with due dates.
Lets say that the administered dates are in F from F9 on and due dates in G from G9 on. Activate cell G9 and enter the following conditions on Conditional Formatting dialog:

Condition 1 (for Red)


Condition 2 (for Yellow)


Condition 3 (for Green)


After you're done, while in G9, activate Format Painter, select the rest of the due dates and let it go.

PS. I used DATEDIF instead of simpler substraction formulas to avoid hassles.



Posted by Chris on July 22, 2001 9:50 AM

No luck. Maybe I said it wrong. Here's what I have

(E9) (G9)(=E9+365)
Date Given Date Due

5-Jul-00 5-Jul-01

If the Date due is = or overdue then make it red. If within 30 days of due, yellow. Otherwise green. I can't figure though how to make it green if say the Date given is 5 Aug 01! Because it's past today so it would be red

Posted by Aladin Akyurek on July 22, 2001 10:44 AM


Are these rules/conditions that you want to apply?

Today's date equal to or greater than due date, then red;

Due date minus todays date is equal to 30 or less, then yellow;

Anything else green.



Posted by Ian on July 22, 2001 1:04 PM

Don't know if your using the formula part of the conditional formating, You don't need to.

I use cell value between =today()-30 and =today()-1

hope this helps


Posted by Chris on July 22, 2001 5:14 PM

:) Yes it is! But what if today's date is 5 Aug 01 and the due date is 5 Aug 02?

Posted by Aladin Akyurek on July 22, 2001 10:31 PM


The procedure is the same as in my initial reply. The formulas have to be modified. Here is the set:

Condition 1 (Red)


Condition 2 (Yellow)


Condition 3 (Green)




Posted by Chris on July 23, 2001 4:47 AM

THANK YOU! It works perfectly