Forum Discussion
ThomasWade
Aug 08, 2021Copper Contributor
Conditional format 30-60-90 days to 1year old
I am trying to automatically highlight a date when it is 30-60-90 days from its due date but I want to use the date a certificate was issued.  Example: I have a cert that was issued on 5-26-2021 and e...
- 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. 
MMyers105
Jun 20, 2023Copper Contributor
If 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!
=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!
HansVogelaar
Jun 21, 2023MVP
For 0 days, use =EDATE(TODAY(),-12) indeed.
But 60 days = 2 months. 1 year = 12 months, so use =EDATE(TODAY(),-10)