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.
Conditional formatting provides visual cues to help you quickly make sense of your data. For example, it’ll clearly show highs and lows, or other data trends based on.
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