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
