Changing cell colour based on date

Hello! I'm so stuck with creating a training matrix! Please help me!

It's a large spreadsheet all on one tab/page. There is a column down the left with staff names, a row at the top with all the modules to complete. Once completed, I enter the date that this module needs to be refreshed by the member of staff. What I want is the following...

- When I enter a date into a cell I want that cell to turn green.
- When there is one month or less until the date in the cell, I want the cell to turn orange (as a warning that the staff members module will need refreshing in less than a month.)
- When the refresher date has arrived/passed, I want the cell to turn red, to let us know that this persons training has now expired.
When there is no date in a cell and it is left blank, I want it to stay white.

Is this even possible? I've managed to do the red one, but not the orange or green!

PLEASE HELP ME IT'S DRIVING ME INSANE!!!

Thank you so much, in advance!

Could use conditional formats

Hello,

Yes that's what I've been trying to do but I don't know how to use them. That's what I need help with really. Knowing exactly what to press and what to type to get the things mentioned above to occur automatically.

What have you got so far; you said you had the red part working?

Hello, so far I have got the following:

For the red rule - Conditional Formatting - 'Format only cells that contain - Cell Value less than =TODAY() - and the colour selected as red.
For the blank rule (so a cell with nothing entered into it remains white) - Conditional Formatting - Format only cells that contain - Blanks - and the colour selected as white.

Then I have done the orange rule but it doesn't work as I want it to. I have gone to Conditional Formatting and then selected 'Format only cells that contain - Dates Occurring - Next month - and the colour selected as Orange. However, say it's the 1st October and the expiry date (date in the cell) is 8th October, it won't turn Orange as it's not 'next month'. It will only turn orange to dates in November. When what I want, is for it to turn Orange when there is less than a month until the expiry date.

Then I really want the other cells to be green once a date is entered into them that has over a month left until it expires. So that it is easier to spot the blanks among the cells that have data entered that aren't less than a month until expiry or past expiry.

Does that make sense?

I've attached a file with the conditional formatting in place.

This is absolutely amazing! Thank you so much!

Now, next question, do I have to change the formula for each cell (changing the I1,2,3 etc) one at a time, or is there a way that I can click a button and apply this formula to the whole spreadsheet?

For context, I have 36 columns of modules along the top and around 50 rows of names down the side and I need these rules to apply to all of the cells, if that makes sense.

Usually can do it by either selecting the cells before creating the format conditions, or copying a cell with the formatting and using paste special to paste only the formats.

Hmm the orange one doesn't seem to work. On your spreadsheet the date in the orange one should be 14/11/2024 or sooner for it to be orange, yours is 17/11/2024.

Maybe change the orange condition to

Formula:

Please Login or Register  to view this content.