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