How do you change cell colour based on date

samdawn88 2 Posts Wednesday March 7, 2018Registration date March 8, 2018 Last seen - Mar 7, 2018 at 03:57 AM - Latest reply: Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen
- 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

3 replies

Best answer
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 8, 2018 at 03:01 PM
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

Thank you, Mazzaropi 1

Something to say? Add comment

CCM has helped 1799 users this month

Respond to Mazzaropi
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 7, 2018 at 11:51 AM
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 2 Posts Wednesday March 7, 2018Registration date March 8, 2018 Last seen - Mar 8, 2018 at 03:29 AM
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