Change cell colors based on date (conditional formatting)

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

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.

If my reply answers your question then please mark as "Answer", it would help others to find their solution easily from your experience.

Thanks
https://afaysal.blogspot.com

19 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated August 12, 2020 Views 11,700 Applies to: