SOLVED

Conditional format 30-60-90 days to 1year old

%3CLINGO-SUB%20id%3D%22lingo-sub-2624632%22%20slang%3D%22en-US%22%3EConditional%20format%2030-60-90%20days%20to%201year%20old%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2624632%22%20slang%3D%22en-US%22%3EI%20am%20trying%20to%20automatically%20highlight%20a%20date%20when%20it%20is%2030-60-90%20days%20from%20its%20due%20date%20but%20I%20want%20to%20use%20the%20date%20a%20certificate%20was%20issued.%3CBR%20%2F%3EExample%3A%20I%20have%20a%20cert%20that%20was%20issued%20on%205-26-2021%20and%20expires%201%20year%20from%20that%20date.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20the%20photo%2C%20I%20want%20that%20cell%20to%20highlight%20when%20today%20is%20less%20than%2030-60-90%20days%20from%2026-may-2022.%3CBR%20%2F%3EI%20want%20to%20keep%20the%20issued%20date%20in%20the%20cell%20but%20I%20want%20it%20to%20highlight%20when%20it%20is%2030-60-90%20days%20from%20its%201%20year%20expiry%20date(without%20having%20to%20add%20expiry%20date.%20I%20don't%20want%20more%20info%20than%20needed.)%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2624632%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2624935%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20format%2030-60-90%20days%20to%201year%20old%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2624935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1123244%22%20target%3D%22_blank%22%3E%40ThomasWade%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20cells%20that%20you%20want%20to%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20drop-down%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'less%20than%20or%20equal%20to'%20from%20the%20second%20drop-down.%3C%2FP%3E%0A%3CP%3EIn%20the%20box%20next%20to%20it%2C%20enter%20the%20formula%20%3DEDATE(TODAY()%2C-9)%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20yellow.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20the%20above%20steps%2C%20but%20with%20the%20formula%20%3DEDATE(TODAY()%2C-10)%20and%20orange%20as%20fill%20color.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFinally%2C%20repeat%20them%20again%2C%20this%20time%20with%20the%20formula%20%3DEDATE(TODAY()%2C-11)%20and%20red%20as%20fill%20color.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2624979%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20format%2030-60-90%20days%20to%201year%20old%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2624979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1123244%22%20target%3D%22_blank%22%3E%40ThomasWade%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20permission%20of%20all%2C%20here%20are%20some%20additional%20examples%20for%20the%20solution%20proposed%20by%20Mr.%20Hans%20Vogelaar.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626832%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20format%2030-60-90%20days%20to%201year%20old%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626832%22%20slang%3D%22en-US%22%3ESo%20simple%20and%20I%20still%20missed%20it%20originally.%20Thank%20you!%20I%20definitely%20take%20a%20step%20away%20from%20the%20excel%20work%20more%20often.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2660914%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20format%2030-60-90%20days%20to%201year%20old%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2660914%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BWhat%20formula%20would%20I%20use%20to%20make%20a%20date%20change%20color%20after%2015%20and%2030%20days%3F%20%3DEDATE(today()%2C-15)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
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.)
7 Replies
best response confirmed by ThomasWade (New Contributor)
Solution

@ThomasWade 

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.

@ThomasWade 

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)

So simple and I still missed it originally. Thank you! I definitely take a step away from the excel work more often.

 

@Hans Vogelaar What formula would I use to make a date change color after 15 and 30 days? =EDATE(today(),-15) 

@karbley 

=A1 > TOADY() - 15
So, 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

@karbley 

Yes. One colour - one rule. To the cell or to the range, depends on your data.