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? 



Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!