SOLVED

Highlighting an anniversary date for 30 days

Copper 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 (Copper 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!!!

1 best response

Accepted Solutions
best response confirmed by Life_is (Copper 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)

View solution in original post