SOLVED

Highlighting an anniversary date for 30 days

%3CLINGO-SUB%20id%3D%22lingo-sub-1994387%22%20slang%3D%22en-US%22%3EHighlighting%20an%20anniversary%20date%20for%2030%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994387%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20applied%20this%20conditional%20formatting%20%3D(MONTH(TODAY())%3DMONTH(B5))*(DAY(TODAY())%3DDAY(B5))%20and%20it%20works%20like%20a%20charm!!%20However%2C%20I%20cannot%20seem%20to%20figure%20out%20how%20to%20add%20in%20the%20formula%20for%20the%20cell(s)%20to%20stay%20highlighted%20for%2030%20days.%26nbsp%3B%20I%20added%26nbsp%3B%2B(DAY(30))%2C%20but%20it%20highlighted%20everything%20with%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3EShawn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1994387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1994651%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20an%20anniversary%20date%20for%2030%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F904108%22%20target%3D%22_blank%22%3E%40Life_is%3C%2FA%3E%26nbsp%3Bthere%20are%20lots%20of%20ways%20to%20do%20it%2C%20but%20how%20about%3A%3C%2FP%3E%3CP%3E%3D(ABS(TODAY()-DATE(YEAR(TODAY())%2CMONTH(B5)%2CDAY(B5))-15)%26lt%3B%3D15)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1994652%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20an%20anniversary%20date%20for%2030%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F904108%22%20target%3D%22_blank%22%3E%40Life_is%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20care%20if%20it%20stays%20on%20for%20ever%3C%2FP%3E%3CP%3E%3D%3CSPAN%3ETODAY%3C%2FSPAN%3E()%26gt%3B%3D(B5-30)%3C%2FP%3E%3CP%3EOr%2C%20if%20you%20want%20%2B-%2030%20days%3C%2FP%3E%3CP%3E%3DABS(%3CSPAN%3ETODAY%3C%2FSPAN%3E()-B5)%26lt%3B%3D30%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20if%20you%20want%20the%20warning%20to%20go%20of%20once%20the%20date%20is%20passed%3C%2FP%3E%3CP%3E%3DAND(TODAY()%26gt%3B%3D(B5-30)%2CTODAY()%26lt%3B%3DB5)%3C%2FP%3E%3CP%3E%3D(TODAY()%26gt%3B%3D(B5-30))*(TODAY()%26lt%3B%3DB5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1995235%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20an%20anniversary%20date%20for%2030%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1995235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857441%22%20target%3D%22_blank%22%3E%40MindreVetande%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20for%20your%20response%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20an%20employee's%20hire%20date%20as%2012%2F1%2F2018%20in%20B5%20I%20need%20another%20cell%20to%20stay%20highlighted%20for%2030%20days%20%22after%22%20the%20current%20date%20of%2012%2F1%2F2020%20is%20reached.%3C%2FP%3E%3CP%3EThis%20formulas%20works%20to%20highlight%20the%20cell%2C%20but%20for%20only%20the%20one%20day%20of%20the%20anniversary%20..%20somewhere%20in%20this%20formula%20I%20should%20be%20able%20to%20add%20%2B30%20days%2C%20I've%20tried%20several%20options%2C%20I%20just%20can't%20seem%20to%20make%20it%20work.%3C%2FP%3E%3CP%3E%3D(MONTH(TODAY())%3DMONTH(B5))*(DAY(TODAY())%3DDAY(B5))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have applied this conditional formatting =(MONTH(TODAY())=MONTH(B5))*(DAY(TODAY())=DAY(B5)) and it works like a charm!! However, I cannot seem to figure out how to add in the formula for the cell(s) to stay highlighted for 30 days.  I added +(DAY(30)), but it highlighted everything with a date.

 

Any help is greatly appreciated.

Shawn

5 Replies
Best Response confirmed by Life_is (New Contributor)
Solution

@Life_is there are lots of ways to do it, but how about:

=(ABS(TODAY()-DATE(YEAR(TODAY()),MONTH(B5),DAY(B5))-15)<=15)

@Life_is 

If you don't care if it stays on for ever

=TODAY()>=(B5-30)

Or, if you want +- 30 days

=ABS(TODAY()-B5)<=30

 

Or if you want the warning to go of once the date is passed

=AND(TODAY()>=(B5-30),TODAY()<=B5)

=(TODAY()>=(B5-30))*(TODAY()<=B5)

 

 

 

@MindreVetande Thank you so much for your response

 

For example, I have an employee's hire date as 12/1/2018 in B5 I need another cell to stay highlighted for 30 days "after" the current date of 12/1/2020 is reached.

This formulas works to highlight the cell, but for only the one day of the anniversary .. somewhere in this formula I should be able to add +30 days, I've tried several options, I just can't seem to make it work.

=(MONTH(TODAY())=MONTH(B5))*(DAY(TODAY())=DAY(B5))

@mtarler  you are the best!!! Thank you so much!!! That did it!!!  I have been at this for several hours!! Thank you! Thank you! Thank you!

@MindreVetande 

 

Thanks so much for your input!!! .. I really appreciate it!!!