Change cell colors based on date (conditional formatting)

Question

Hello,

i am using excel 2016.  I have a set of employees who take tests at different times of the year. these tests have 2 expiration dates due to the type of test. some expire in 3 years and some expire in 1 year.  I would like to enter formatting that changes the cells background fill color when the expiration date is approaching. the dates input are when the test was taken.

ex, john takes a test on march 23 2016 and it expires in 1 year. in the cell lets say cell A2 I input the date he took the test show it shows 3/23/2016

in one year the test would expire on 3/23/2017 and I would like to know when that date is approaching by way of formatting a color change

I would like to

have the cell format to a green background from the date I input the test date up until there is 61+ days left when it will expire

have the cell format to a orange background when there ids 60 to 31 days left until expiration

have the cell format to a red background when there is less than 30 days left until expiration

the same holds true for my tests with 3 year expirations but with the proper format for a 1 year I'm sure I can adjust accordingly to make it work. I have tried many of the older coding and ihasnt worked ex using a format to change color by going to home-> conditional formatting ->fomat cell with this value --> =TODAY-335 to create red or =TODAY()-335 to create red etc

please help

Answer 1

Refer below image:

• Select A2 cell and apply 3 conditional formatting rules from Conditional Formatting | New Rules | Use a formula to determine which cells to format.

For Green Status:

• Enter this formula and select Green color from Format button

=TODAY()-DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))>=61

For Orange Status:

• Enter this formula and select Orange color from Format button

=TODAY()-DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))>=31

For Red Status:

• Enter this formula and select Red color from Format button

=TODAY()-DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))>=0

Check the conditional formatting rules priority from Conditional Formatting | Manage Rules. This will looks like below:

Now Click on Ok.