How do you change cell colour automatically based on date. I am trying to set my training spreadsheet so I can clearly see when staff members are due or have upcoming training.
I am looking to do the following:
Due to renew in 30 days Orange
In date Yellow
Most of the training is renewed every 3 years and I would have the completed training date in the cells. So if training was completed on 07/03/2015 it should show red as it has become overdue today, and will continue to show red until updated with new training date. If the training date was 29/03/15 then it would be orange as its due to be renewed in the next 30 days. Anything else should show as green. What kind of formula could I use to make this happen?
There will be 3 rules to apply the different colors as you wish.
Red: The current day is greater than or equal to the 3 year term of the training completion date.
Orange: The current day is less than or equal to 30 days of expiration of the 3 year term of the training completion date.
Green: The current day is greater than 30 days from the expiration of the 3 year term of the training completion date.
I made an example by applying the rules of the desired conditional formatting.
I created a guide just to study separately the rules of each case.
Of course it can and should be deleted. It's just for didactic effect for you.