Forum Discussion
Conditional Formatting for dates
So I have made an example of what I have. The dates in the boxes are expiry dates. Should i have the dates in there for when the ticket was acquired? I would like to have all formulas the same and green from date acquired, yellow when they reach 2 years 10 months and red on expiry.
anyway i would be happy to find out if I could help.
Nikolino
I know I don't know anything (Socrates)
Thank you so much for taking the time to help me.
This does not seem to work for me. When you write =$I-6 <=TODAY()+60 is that what I fill in or do I write =$May 16, 2022<=Today(). Once I try to add the last rule =$I-6>=TODAY() it does not allow me to do it and asks me to add a ' in front of the =.
Daveyboy1981 conditional formatting custom formulas can be a little tricky. Let me see if I can help.
1) in the formula box you type = [condition] and if that [condition] evaluates to be true the formatting will be applied. if false it will not.
2) this is where it can get confusing. that [condition] formula can use absolute cell references (e.g. $a$1) or relative cell references (e.g. a1) or mixed (e.g. a$1). Any relative reference will be relative to the upper left corner of the range(s) that the conditional formatting is being applied.
so what you want is something like the following:
If the area being checked is B2:Z100 then
= B2<=TODAY() [set formatting to green]
meaning: if the date found in the cell is < (before) today then highlight green
and another rule for the same range (B2:Z100) for the yellow:
= B2 >= TODAY()-1035 (which is about 2 yrs 10months) [set to yellow]
or if you want to be more precise:
=B2 >= DATE( YEAR(NOW())-2, MONTH(NOW())-10, DAY(NOW()))
meaning: if the date found in the cell is > (after) 2year and 10months before today then highlight yellow
IF you used B3 for example then when it is checking B2 (the upper left corner) it would be LOOKING at the value in B3 and if B3 met the criteria then it would highlight B2. And this looking at the cell 1 row below it would happen over the entire range of B2:Z100 so cell Z100 would be highlighted IF Z101 met the criteria.
I hope that helps you understand how it works so you can make your formulas work.
Sorry I jump in, just small comment to
1) in the formula box you type = [condition] and if that [condition] evaluates to be true the formatting will be applied. if false it will not.
More exactly trigger works on TRUE (or it's numeric equivalent) and doesn't work on any other value (FALSE, text, error). In addition, if several rules are applied to the same cell/range, condition only in first in sequence rule triggers the formatting for this cell/range.
