Jul 15 2020 08:04 AM
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
Jul 15 2020 08:22 AM
Jul 15 2020 10:19 AM
NAME | Confined Space Entry/Monitor | Crane and Rigging | EWP | Fall Pro | Fire Extinguisher Safety | First Aid | Forklift | Health and Safety Committee Reps | Site Specific WHMIS 2015 |
Steve | April 16, 2022 | February 12, 2023 | February 11, 2023 | May 17, 2022 | April 3, 2020 | October 28, 2019 | |||
Chris | July 7, 2020 | November 20, 2023 | November 19, 2022 | ||||||
Wayne | |||||||||
Arthur | July 7, 2020 | April 23, 2020 | |||||||
Wade | April 16, 2022 | October 27, 2020 | October 6, 2020 | July 12, 2021 | March 6, 2022 | ||||
Dylan | April 16, 2022 | September 23, 2019 | |||||||
Judy | |||||||||
Susan | March 11, 2022 | ||||||||
Jarrod | July 7, 2020 | October 24, 2020 | September 23, 2019 | ||||||
Mark | September 19, 2020 | April 2, 2021 | September 18, 2020 | ||||||
Leslie | April 16, 2022 | February 26, 2023 | March 5, 2022 | April 17, 2020 | April 23, 2020
|
Jul 15 2020 10:24 AM
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.
Jul 15 2020 12:03 PM
anyway i would be happy to find out if I could help.
Nikolino
I know I don't know anything (Socrates)
Jul 16 2020 05:42 AM
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 =.
Jul 16 2020 06:12 AM
@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.
Jul 16 2020 06:33 AM
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.
Jul 16 2020 10:29 AM