Help with Anniversary Formula

marchysj

I have a sheet where I would like a [Next Anniversary] column to display and automatically update the next upcoming anniversary of [Hire Date].

I don't want the [Next Anniversary] column to display the date and month with the current year, because some of the dates have already passed. (ex. if [Hire Date] is 2/5/19, I would like [Next Anniversary] to display 2/5/25 (not 2/5/24 since that date has already passed); if [Hire Date] is 05/23/21, I would like [Next Anniversary] to display 05/23/24).

This is the formula I have right now:

=DATE(YEAR(TODAY()) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Hire Date]@row), DAY([Hire Date]@row)), 1, 0) - IF(MONTH([Hire Date]@row) = 1, 1, 0), IF(MONTH([Hire Date]@row) = 1, 12, MONTH([Hire Date]@row)), DAY([Hire Date]@row))

However, if the [Hire Date] is in January, the [Next Anniversary] is popping up with a December month (see screenshot).

How can I fix this so that [Next Anniversary] for the January hire dates shows "01/X/25"?

Brad Klodowski

From looking at this, there doesn't seem to be a need to subtract anything - if we pseudocode this solution, it might be as follows:

• Find out the month and day of the date in Hire Date
• Build a new date out of that month and day, and the current year
• If that date has already passed, add one to the year and use that as the date instead

You had the right idea with your formula - but basically you want to only ever change the year, not the month or day (since those should always be exactly identical to the user's hire month and day).

The formula I came up with and tested is:

=DATE(YEAR(TODAY()) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Hire Date]@row), DAY([Hire Date]@row)), 1, 0), MONTH([Hire Date]@row), DAY([Hire Date]@row))

Basically, we start building a date with today's year, but use an IF statement to add 1 to the value if the value we come up with is in the past, then just finish out by using the exact month and day of the hire date.

Hope this was helpful! Feel free to let me know if I can explain anything better.

Brad Klodowski

From looking at this, there doesn't seem to be a need to subtract anything - if we pseudocode this solution, it might be as follows:

• Find out the month and day of the date in Hire Date
• Build a new date out of that month and day, and the current year
• If that date has already passed, add one to the year and use that as the date instead

You had the right idea with your formula - but basically you want to only ever change the year, not the month or day (since those should always be exactly identical to the user's hire month and day).

The formula I came up with and tested is:

=DATE(YEAR(TODAY()) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Hire Date]@row), DAY([Hire Date]@row)), 1, 0), MONTH([Hire Date]@row), DAY([Hire Date]@row))

Basically, we start building a date with today's year, but use an IF statement to add 1 to the value if the value we come up with is in the past, then just finish out by using the exact month and day of the hire date.

Hope this was helpful! Feel free to let me know if I can explain anything better.

marchysj

@Brad Klodowski You are a life saver! I was playing around with the subtraction part, but kept getting various errors. Works perfectly now! Thank you 😊