Colour Call by Future Expiry date for training

Question

I am creating a training matrix spreadsheet and i want expiry dates to change colour, so green in date, orange 1 month due to expire, Red Expired as per examples below.

EX1 - Annual Asbestos awareness - Bob completes his awareness course on the 19/02/25. This date is inputted to matrix and shows as Green (Valid) on the 19/01/26 it goes Amber (Due to expire) on the 21/02/26 Goes Red Expired.

EX2 Qualification ( Evergreen) - Completes an apprenticeship that is evergreen(No Expiry) In Puts date stays green.

Some courses are 1 year some 2 year or 3 or evergreen so want to input obtaoned date rather than expire date.

Answer 1

There are two constants that you know for each column: the amount of time after which the training is expired (you mentioned 1 year, 2 years, etc) and the length of the warning period (you mentioned only 1 month but it could be different for different training subjects). For each column, you need to create three conditional formatting rules.

For example, select column D, click on Conditional Formatting, select New Rule and then Use a formula... In the formula box enter =AND(ISNUMBER(D1),TODAY()-D1>365) and select your red formatting. Create the second and third formulas =AND(ISNUMBER(D1),TODAY()-D1>335) and =ISNUMBER(D1) the same way, selecting the appropriate color. Then click on Conditional Formatting and Manage Rules, reorder the rules if necessary and check the Stop If True box for each.

You can replace the second argument of the AND function with a different expression as I showed in my earlier answer. If you want the color coding to be "as of" the date in A1 instead of today, replace calling the TODAY function with a reference to cell A1.

Answer 2

There are two parts to your problem. One is to determine if a completion date is sufficiently far in the past to warrant changing the color. The other is to define what "sufficiently far in the past" means.

For the first part, assume the completion date is in cell C8 and the interval is one year:

• One common approach is to evaluate the expression TODAY()-C8>365. This works most of the time but has problems with leap years and certain month boundaries. This approach can deal with almost any desired interval.
• A more precise approach is to evaluate the expression EDATE(C8,12)<TODAY(). This approach can deal only with intervals that are a whole number of months.

The second part also has different options. If Training-1 is annual and Training-2 is bi-annual and the training title is in B8:

• Evaluate AND(B8="Training-1",TODAY()-C8>365) followed by AND(B8="Training-2",TODAY()-C8>730). (Each conditional formatting rule is evaluated against every cell in the range.)
• You can put the interval in a cell in the same row (E8) and evaluate TODAY()-C8>E8).
• In some unused portion of the workbook, you build a control table with two columns and rows for all the training titles. One column would have the title and the other the interval. You then replace E8 in the previous formula with a lookup expression into this table.