Share via

Anniversary formulas for Excel

Anonymous
Aug 8, 2018, 10:10 PM

Formula to create 1,3 and 5 year anniversaries from employee start date. The formula

=EDATE(date,(DATEDIF(date,as_of,"y")+1)*12) for some reason does not work for me :( I'm sure it's human error, sigh.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. To protect privacy, user profiles for migrated questions are anonymized.

0 comments
Accepted answer
  1. Anonymous
    Aug 9, 2018, 12:50 AM

    Hi Gina,

    If you want to get special anniversary of every staff, you could refer to following formulas.

    Anniversary: {=DATE(YEAR(TODAY()),MONTH(D6),DAY(D6))};

    Anniversary numbers: {=YEAR(E6)-YEAR(D6)};

    Special anniversary:{=IF(OR(F6=3,F6=1,F6=5),F6," ")}.

    For any concerns, please post back.

    Regards,

    Eli

    43 people found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 99,490 Reputation points Volunteer Moderator
    Aug 9, 2018, 12:03 AM

    Hi,

    Not sure what you want but try this

    =EDATE(joining_date,12)

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    Aug 10, 2018, 8:01 AM

    Hi Gina,

    May I know if our suggestions help you? If you need further assistance regarding anniversary formulas, feel free to let us know.

    Regards,

    Eli

    0 comments
  3. Anonymous
    Aug 15, 2018, 5:24 PM

    Thanks so much Eli for the helpful information. When I used the special anniversary formula, the cell appeared blank. I'm assuming its user error and will keep trying.

    0 comments
  4. Anonymous
    Aug 16, 2018, 1:17 AM

    Hi Gina,

    Glad to know the formulas are helpful.

    You mentioned that "When I used the special anniversary formula, the cell appeared blank". Normally, if the year is not the special anniversary (like 1, 3, 5), it will display blank. It depends on the formula {=IF(OR(F6=3,F6=1,F6=5),F6,"<you could edit here to display blank or other information> ")}.

    If the year has been the special anniversary, please check if the formula has matched the right cells.

    Regards,

    Eli

    0 comments