Forum Discussion
Stuck with Conditional Formatting
I've searched high and low and not getting anywhere I need. It's probably so simple but I just can't get it to work.
I've got a training matrix document.
I need a column to adjust the color based on upcoming expiry dates for tests taken.
Scenario:
Column A has a list of names of people to take these tests.
Column B Row 1 has a title header and then from Row 2 onwards a list of dates a test was taken. These all expire within 2 years.
Column C Row 1 has a title header and then from Row 2 onwards a list of dates another test was taken. These all expire within 3 years.
For example, if we use today's date 26/11/2022
| Name | Test A (2 years expiry) | Test B (3 years expiry) |
| John Smith | 26/11/2022 (Green) | 26/11/2022 (Green) |
| Alex Jones | ( No Entry Blank) | 26/12/2019 (Orange) |
| Katie Jones | 26/12/2020 (Orange) | 26/10/2019 (Red) |
I need to set the formatting so that:
No entry into the column - Blank Cell
The date entered is current and within the parameter of 2 years or 3 years from Date entered = Green
The date entered is nearing 30 days of expiry from the date entered = Orange
The date entered is expired from the date entered = Red
7 Replies
Select the cells in column B, starting with B2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down,
Enter 0 in the box next to it.
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
Repeat these steps, but instead of 0, enter the formula =EDATE(TODAY(),-24) in the box, and select orange as fill color.
Finally repeat them again with the formula =EDATE(TODAY(),-23) and green as fill color.
For the range in column C, starting in row 2, follow the same instructions, but with the formula =EDATE(TODAY(),-36) for orange and =EDATE(TODAY(),-35) for green.
CosTech1955 As a variant and a bit easier, I think. Note I introduced the number of years before expiry into separate cells (B1 and B2) to make it more dynamic.
File attached.
CosTech1955 My date settings are different, but the logic is the same.
It seems you will need two sets of conditional formats; here is one set:
In text form, those formulas are:
=IF( (C2<>"")*(TODAY()-(C2+1095) > 0 ), TRUE ) =IF( (C2<>"")*(TODAY()-(C2+1095) >= -30 ), TRUE ) =IF( (C2<>"")*(TODAY()-(C2+1095) < -30 ), TRUE )Obviously, the other set of formulas would use 730 for two years.
I presume you do not require precision that includes leap days within those two or three years.
- This is actually perfect! If I understood a little about how it worked.
Is it possible to just break down the formula (still relatively new to conditionals) so I understand what's going on as such? For instance, I'd need to change the C value for the B column right?- That's correct, you would change the C2 to B2 when you type the two-year formula, as well as changing 1095 to 730.
The portion (C2<>"") prevents the formatting from being applied to blank cells. The remaining portion calculates the expiry date (C2+1095) and subtracts that from the current date ( TODAY() ) to see how many days away that is.
