Formula- anniversary date?

QuestionEdit

QUESTION: Hi there.. I have a formula to recognize the anniversary dates of my employees of a specified period (1yr, 5yr etc)...whatever I plug in. I am looking for a way to modify this formula to highlight and recognize anyone's anniversary that is today or within the next upcoming 5 days and just not only the "exact" date. Is there anyway I could add this to the below formula:

=IF(AND(MONTH(A1)=MONTH(TODAY()),DAY(A1)=DAY(TODAY()),YEAR(A1)=YEAR(TODAY())-1),TRUE,FALSE)

Thank you for your help and time! Brett

ANSWER: try this one and see how it goes

=IF(AND((TODAY()-DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)))>=0,(TODAY()-DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)))<=5),TRUE,FALSE)

---------- FOLLOW-UP ----------

QUESTION: Thanks Ricardo.. maybe you have a better formula for what I am trying to do..or suggestions.. My goal is to have a formula that will look in my C2 column and using today's date and conditional formatting highlight the cell of an employee who is going to have a 1yr, 5yr, 10yr etc anniversary. My original formula does that, however I would like it to recognize the 1yr, 5yr, 10yr anniversaries before the actual day hits so it gives me time to recognize them before I go into a day off etc.. Right now if I am not working that day I miss it because it only highlights the actual date it happens..so I was looking for something that will still recognize their date but highlight it on that day or 5 days earlier (keeping in mind the 1yr, 5yr, 10yr mark) so I can do something special. I hope that makes a little more sense. Sorry for the confusion. :-(

AnswerEdit

Hi Brett

Assuming: 1.- on column C you have all your employees starting dates 2.- you only wish to be notified of 1yr, 5yr and 10yr

lets begin with cell C2: Go to Format - Conditional formatting and you will set up two conditions

1.- cell value is between =(DATE(YEAR(C2),MONTH(TODAY()),DAY(TODAY()))-5)*--OR((YEAR(TODAY())-YEAR(C2))=1,(YEAR(TODAY())-YEAR(C2))=5,(YEAR(TODAY())-YEAR(C2))=10) and¡­ =(DATE(YEAR(C2),MONTH(TODAY()),DAY(TODAY()))-1)*--OR((YEAR(TODAY())-YEAR(C2))=1,(YEAR(TODAY())-YEAR(C2))=5,(YEAR(TODAY())-YEAR(C2))=10)

2.- cell value is equal to: =(DATE(YEAR(C6),MONTH(TODAY()),DAY(TODAY())))*--OR((YEAR(TODAY())-YEAR(C6))=1,(YEAR(TODAY())-YEAR(C6))=5,(YEAR(TODAY())-YEAR(C6))=10)

Set up the format as you like Now the date will change colour when the anniversary is approaching and will change on that exact date as well

Additionally, you can set up an additional cell on row 2 with this formula: =IF(AND((TODAY()-DATE(YEAR(TODAY()),MONTH(C2),DAY(C2)))>=0,(TODAY()-DATE(YEAR(TODAY()),MONTH(C2),DAY(C2)))<=5),YEAR(TODAY())-YEAR(C2),)*--OR((YEAR(TODAY())-YEAR(C2))=1,(YEAR(TODAY())-YEAR(C2))=5,(YEAR(TODAY())-YEAR(C2))=10) This formula will tell you which period you will recognize to the employee

that just was the cell C2, now you have to copy it over the rest of the cells.

If you need to set up additional periods, you'll have to do so after the *--OR part of the formula

I hope this is what you are looking for