A spreadsheet where the cell automatically changes when the mandatory training is nearing expiration and then again when they have expired

Question

A spreadsheet where the cell automatically changes when the mandatory training is nearing expiration and then again when they have expired

Luke Williams 0

I just want to make an excel spreadsheet where the cell automatically changes when the mandatory training is nearing expiration and then again when they have expired.

for instance.

i have a date of 01/07/2023, i would like this green. then one month from expiry id like it automatically turned yellow, then after expiry of 6 months id like it to turn red. Therefore informing managers that training is out of date. After training given a new date is to be inputted and then turned green again.

If this makes any sense, some help would be greatly appreciated.

Sanmeet Gurudatta 0 Reputation points

2023-10-22T11:13:19.8766667+00:00
hello Luke,

Hope your day is good ;

To achieve the desired behavior in Excel, you can use Conditional Formatting with custom formulas. Here's a step-by-step guide to set up the rules:

Green Highlight (for the original date)

Select the cell(s) you want to apply the formatting to, let's say A1.

Go to "Home" tab > "Conditional Formatting" > "New Rule".

Choose "Use a formula to determine which cells to format".

Enter the formula =AND(A1<>"", ISNUMBER(A1)).

Click "Format", choose a green fill, and then click OK.

Yellow Highlight (for dates that are one month away from expiry)

With the same cell(s) selected, go to "Conditional Formatting" > "New Rule".

Use the formula: =AND(A1<>"" , ISNUMBER(A1), TODAY()>=EDATE(A1,-1), TODAY()<=A1).

Choose a yellow fill.

Red Highlight (for dates that have expired by 6 months)

With the cell(s) selected, go to "Conditional Formatting" > "New Rule".

Use the formula: =AND(A1<>"" , ISNUMBER(A1), TODAY()>EDATE(A1,6)).

Choose a red fill.

Ensure the rules are applied in the order specified. You can manage the order of the rules in "Conditional Formatting" > "Manage Rules". Move the red rule to the top, followed by the yellow rule, and then the green rule. Rules are applied from top to bottom, so this order ensures that the most specific condition (red) takes precedence over the general ones (yellow and green).

Remember to adjust the cell reference (A1) if you're applying this to a different cell or a range of cells.
Luke Williams 0 Reputation points

2023-10-22T11:16:50.22+00:00

Great, thank you. Forgive me as im not clued up on formulas as such. What do you mean by 'ISNUMBER' do i need to put my own value in there at all?
Barry Schwarz 5,756 Reputation points

2023-10-22T14:53:21.69+00:00

ISNUMBER is an Excel builtin function. It returns TRUE if the value is a number, FALSE otherwise. Its use in these formulas ensures that the conditional formatting is not applied to non-numeric data.