How do you change cell colour based on dateClosed

Question

Hello, 

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:

Overdue                                    Red

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? 

Thanks

Mazzaropi · Answer

samdawn88, Good afternoon. You can use Conditional Formatting to colour your cells depending with some dates. Suppose you cell date is A2 Formula --> =$A$2<=TODAY() Colour RED Formula --> =AND($A$2-TODAY()>0, $A$2-TODAY()<=30) Colour ORANGE Formula --> =$A$2-TODAY()>30 Colour GREEN For quick and effective help, save your spreadsheet to a free website, www.sendspace.com, and place the link here for download. Seeing the layout of the worksheet will make it easier for all of us to help you in a more concrete way. -- Belo Horizonte, MG - Brasil. Marcílio Lobão

Mazzaropi · Answer

Sam, Good afternoon.

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.

https://www.sendspace.com/file/y87cfu

As you can see, there is currently no date that will appear a color other than green color.
So you just have to modify some date to see how it works. 
Do some testing.

Was that what you wanted?
I hope I've helped. 
 --
Belo Horizonte, MG - Brasil.
Marcílio Lobão

How do you change cell colour based on date

2 responses

Similar discussions

Subscribe To Our Newsletter!