Help with Anniversary Formula
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"?
Best Answer
-
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.
Answers
-
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 You are a life saver! I was playing around with the subtraction part, but kept getting various errors. Works perfectly now! Thank you 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!