Formula for yearly anniversary with month and day only
I want to set up reminders for annual review alerts. The annual review date is a formula of Completion Date+365 days, but I don’t want to have to update the year each time. I just want to put in a completion date of 2 May 2022 and have the Anniversary column return the month and day so I can get an auto-reminder four weeks before 2 May every year. Is it possible to do what I want to do?
Thanks,
Ashley
Best Answer
-
@Ashley McAdoo After re-reading your original post, I realize you specified 4 weeks. In that case it would be simply outputting the date 1 year from now and then subtracting 28 (days) from it.
=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), MONTH([Completion Date]@row), DAY([Completion Date]@row)), 1, 0), MONTH([Completion Date]@row), DAY([Completion Date]@row)) - 28
Answers
-
Hello @Ashley McAdoo
So If I am understanding this correctly, you want to input the completion date, and have every year an alert for that date one month before?
You could do that by making an anniversary column and an alert workflow when the date occurs.
Column formula for anniversary one month prior:
=DATE(YEAR(TODAY()), MONTH([completion date]@row) - 1, DAY([completion date]@row))
Hope this helps!
-
Hey Christian,
I had tried a formula similar to that and it did return the anniversary date, but the date format was 6/2/2022. I also need a reminder for 2023, 2024, etc. How do I get a yearly reminder every May 2, not just for the first anniversary?
Thanks,
Ashley
-
Hi,
Please could i have a formula for 7 days before?
Thanks
-
Hey @Alexandra Amies
I think you should be able to change the MONTH part to DAY
=DATE(YEAR(TODAY()), MONTH([completion date]@row) - 1, DAY([completion date]@row))
=DATE(YEAR(TODAY()), DAY([completion date]@row) - 7)
Let me know if that works.
Regards,
Ashley
-
Using the TODAY function will automatically update the year based on today's date. However... It will show 6/2/2022 until January 1 of 2023. If you want it to change on the day after the reminder triggers, we would need to adjust to this:
=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), MONTH([Completion Date]@row), DAY([Completion Date]@row)), 1, 0), MONTH([Completion Date]@row) - 1, DAY([Completion Date]@row))
The other challenge with this is that any dates in January will error out because (month number) 1 minus 1 equals zero which is not a valid month number for the date function. To accommodate this (and accounting for the above), we would use something more like this:
=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), MONTH([Completion Date]@row), DAY([Completion Date]@row)), 1, 0) - IF(MONTH([Completion Date]@row) = 1, 1, 0), IF(MONTH([Completion Date]@row) = 1, 11, MONTH([Completion Date]@row) - 1), DAY([Completion Date]@row))
-
@Ashley McAdoo After re-reading your original post, I realize you specified 4 weeks. In that case it would be simply outputting the date 1 year from now and then subtracting 28 (days) from it.
=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), MONTH([Completion Date]@row), DAY([Completion Date]@row)), 1, 0), MONTH([Completion Date]@row), DAY([Completion Date]@row)) - 28
-
This would be the same as my last comment to Ashley above except you would subtract 7 instead of 28.
-
Awesome that worked well- thank you so much
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
