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)

=DATEDIF(G9,TODAY(),"d")>=0

Condition 2 (for Yellow)

=DATEDIF(G9,TODAY(),"d")=30

Condition 3 (for Green)

=DATEDIF(G9,TODAY(),"d")>30

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.

Aladin

==========


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

Chris,

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.

Aladin

===========


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

Ian

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

Chris,

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

Condition 1 (Red)

=AND(YEAR(G2)=YEAR(TODAY()),DATEDIF(G2,TODAY(),"d")>=0)

Condition 2 (Yellow)

=AND(YEAR(G2)=YEAR(TODAY()),DATEDIF(TODAY(),G2,"d")<=30)

Condition 3 (Green)

=DATEDIF(TODAY(),G2,"d")>30

Aladin

====================


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

THANK YOU! It works perfectly