How do you change cell colour based on date

Posts
2
Registration date
Wednesday March 7, 2018
Last seen
March 8, 2018
- - Latest reply: Mazzaropi
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
- Mar 8, 2018 at 03:01 PM
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
See more 

Your reply

2 replies

Best answer
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
1
Thank you
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

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2763 users this month

Respond to Mazzaropi
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
0
Thank you
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
samdawn88
Posts
2
Registration date
Wednesday March 7, 2018
Last seen
March 8, 2018
-
Thanks for your response. Which Rile type to I need to select though, is it the 'Use a formula to determine which cells to format'?

My spreadsheet can be seen via https://www.sendspace.com/file/zjxaua, if this helps.

Thanks
Sam
Respond to Mazzaropi