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!!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
formatting cells to change color based on number of days from date in cell
0
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:
Select All=$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:
Select All=$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.