Forum Discussion
Solved
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 expires 1 year from that date.
In the photo, I want that cell to highlight when today is less than 30-60-90 days from 26-may-2022.
I want to keep the issued date in the cell but I want it to highlight when it is 30-60-90 days from its 1 year expiry date(without having to add expiry date. I don't want more info than needed.)
Example: I have a cert that was issued on 5-26-2021 and expires 1 year from that date.
In the photo, I want that cell to highlight when today is less than 30-60-90 days from 26-may-2022.
I want to keep the issued date in the cell but I want it to highlight when it is 30-60-90 days from its 1 year expiry date(without having to add expiry date. I don't want more info than needed.)
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.
With permission of all, here are some additional examples for the solution proposed by Mr. Hans Vogelaar.
Thank you for your time.
Nikolino
I know I don't know anything (Socrates)
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.
- 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!For 0 days, use =EDATE(TODAY(),-12) indeed.
But 60 days = 2 months. 1 year = 12 months, so use =EDATE(TODAY(),-10)
HansVogelaar 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?
3 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.
HansVogelaar What formula would I use to make a date change color after 15 and 30 days? =EDATE(today(),-15)
=A1 > TOADY() - 15
