Forum Discussion
Changing a different cell colour depending on date
so red would be well overdue (ie a month), amber just overdue (a week or so) and green would be due or taken place!
As far as I can tell, that wouldn't work the way you want. Let's take an example. Today is 25/05/2023.
A cell contain 20/04/2023. This is more than a month ago, so the cell is red.
Now the supervision takes place today, so you change the date to 25/05/2023. The cell automatically becomes green, as intended.
But tomorrow, 25/05/2023 is in the past, so the cell becomes yellow, and at the end of June, it would become red.
An alternative could be to enter a date in the future, for example the date the next supervision should take place, or if that is not applicable, a 31/12/2099 or so.
Or you could enter the date the supervision took place in another cell.
Do you have a preference?
OK, select the cells with the dates.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Icon Sets' from the 'Format Style' drop down.
Select Formula from both drop downs under Type.
Enter =TODAY() in the first box under Value, and =TODAY()-7 in the second box under Value.
Click OK.
- I think the option of putting the date in the future would be good as supervisions normally take place every three months