Forum Discussion
Conditional format 30-60-90 days to 1year old
- Aug 08, 2021Select the cells that you want to format. On the Home tab of the ribbon, click Conditional Formatting > New Rule... Select 'Format only cells that contain'. Leave the first drop-down set to 'Cell Value'. Select 'less than or equal to' from the second drop-down. In the box next to it, enter the formula =EDATE(TODAY(),-9) Click Format... Activate the Fill tab. Select yellow. Click OK, then click OK again. Repeat the above steps, but with the formula =EDATE(TODAY(),-10) and orange as fill color. Finally, repeat them again, this time with the formula =EDATE(TODAY(),-11) and red as fill color. 
Select the cells that you want to format.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop-down set to 'Cell Value'.
Select 'less than or equal to' from the second drop-down.
In the box next to it, enter the formula =EDATE(TODAY(),-9)
Click Format...
Activate the Fill tab.
Select yellow.
Click OK, then click OK again.
Repeat the above steps, but with the formula =EDATE(TODAY(),-10) and orange as fill color.
Finally, repeat them again, this time with the formula =EDATE(TODAY(),-11) and red as fill color.
- MMyers105Jun 20, 2023Copper ContributorIf I wanted the cell to turn red when the date was zero days from the due date would I use
 =EDATE(TODAY(),-12)
 I also would like all the cells that have 60 or more days until due to have no fill would I use
 =EDATE(TODAY(),-9)
 Thanks!- HansVogelaarJun 21, 2023MVPFor 0 days, use =EDATE(TODAY(),-12) indeed. But 60 days = 2 months. 1 year = 12 months, so use =EDATE(TODAY(),-10) 
 
- DieselWSMFeb 03, 2023Copper ContributorHansVogelaar This is an older thread but useful! Thanks. I have the same question but what is the formula when the expiration is 3 or 5 years from the date entered in the cell? - HansVogelaarFeb 03, 2023MVP3 years is 36 months, so 3, 2 and 1 months before the expiration date is 33, 34 and 35 months from the start date. So use =EDATE(TODAY(),-33) etc. 5 years is 60 months, so 3, 2 and 1 months before the expiration date is 57, 58 and 59 months from the start date. So use =EDATE(TODAY(),-57) etc. - DieselWSMFeb 03, 2023Copper ContributorThanks!
 
 
- karbleyAug 18, 2021Copper ContributorHansVogelaar What formula would I use to make a date change color after 15 and 30 days? =EDATE(today(),-15) - SergeiBaklanAug 18, 2021Diamond Contributor=A1 > TOADY() - 15- karbleyAug 18, 2021Copper ContributorSo, I am applying three rules to these cells?
 1. cell is green
 2. cell changes to yellow after 15 days
 3. cell changes to red after 30 days
 
 
- ThomasWadeAug 09, 2021Copper ContributorSo simple and I still missed it originally. Thank you! I definitely take a step away from the excel work more often.