formatting cells to change color based on number of days from date in cell

0

I am trying to make a spreadsheet to track my employees certification dates. I would like to be able to set up the sheet to change color to yellow if the certifiaction date is 90 days or less away and then change to red if the date is less than 30 days away. I have tried condional formatting but I get no where. Help please!!

Answer
Discuss

Answers

0

Conditional formatting is what you need and you need to use a formula for it.

Make sure you have one cell that has the certification date in it and that it is formatted as a date.

Then, select the cell that you want to change colors and go to the Home tab > click Conditional Formatting > New Rule > go to Use a formula to determine which cells to format and then enter this formula:

=$B$1-TODAY()<=90

and click the Format button and choose yellow for the Fill color. Hit the OK button when you're done.

Follow the same steps again but this time enter this formula:

=$B$1-TODAY()<=30

and choose red for the Fill color formatting.

In both formulas, B1 is the cell that contains the certification date, so change that as needed.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login