Conditional Formatting for dates

Copper Contributor

I have a spreadsheet with names going down on the left side column, at the top going right in the columns I have courses people have taken. In each intersecting cell there is the date of expiry for the course. Each course has a 3 year expiry, and everyone takes these courses on different dates. I would like to know if it is possible to use conditional formatting to have valid tickets green, change colors to alert me 2 months prior to expiry, and then turn red when they expire. I have watched a few videos, but they all seem to have dates in the same column. Please help

8 Replies
examble for Contitional Formating

1) =$I$6<=TODAY() +60 Explain : I6 = Expiry Date , TODAY Date is automaticaly from systen generate, +60 Explain: is the 2 months option for info before expire.
2) =$I$6<=TODAY() Explain: the same end date if is expire
3) =$I$6>=TODAY() Explain: if the ticket not are expired

I would be happy to find out if I could help.

Nikolino
I know I don't know anything (Socrates)

@NikolinoDE 

NAMEConfined Space Entry/MonitorCrane and RiggingEWPFall ProFire Extinguisher SafetyFirst AidForkliftHealth and Safety Committee RepsSite Specific WHMIS 2015
Steve April 16, 2022February 12, 2023February 11, 2023 May 17, 2022April 3, 2020 October 28, 2019
Chris July 7, 2020November 20, 2023November 19, 2022     
Wayne         
Arthur July 7, 2020      April 23, 2020
Wade April 16, 2022October 27, 2020October 6, 2020 July 12, 2021March 6, 2022  
Dylan April 16, 2022      September 23, 2019
Judy         
Susan March 11, 2022       
Jarrod July 7, 2020   October 24, 2020  September 23, 2019
MarkSeptember 19, 2020 April 2, 2021September 18, 2020     
Leslie April 16, 2022   February 26, 2023March 5, 2022April 17, 2020

April 23, 2020

 

@NikolinoDE 

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.

@Daveyboy1981 

 

a small example in the file ... i am not sure if that is what you are looking for ... because the translation in german is not the best ... if not that what you are looking for, please give me a short message ...
if this is what you are looking for, please give me short message with a thumbs up :)
 
 

anyway i would be happy to find out if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

@NikolinoDE

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.

@mtarler 

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. 

I couldn't explain it better ... thank you :)