Formula for yearly anniversary with month and day only

Ashley McAdoo

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

Paul Newcome

@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

Christian Graf

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!

Ashley McAdoo

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

Alexandra Amies

https://community.smartsheet.com/discussion/comment/328135#Comment_328135

Hi,

Please could i have a formula for 7 days before?

Thanks

Alexandra Amies

https://community.smartsheet.com/discussion/comment/394690#Comment_394690

@Christian Graf

Ashley McAdoo

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

Paul Newcome

https://community.smartsheet.com/discussion/comment/328147#Comment_328147

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))

Paul Newcome

@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

Paul Newcome

https://community.smartsheet.com/discussion/comment/394690#Comment_394690

This would be the same as my last comment to Ashley above except you would subtract 7 instead of 28.

Alexandra Amies

Awesome that worked well- thank you so much

Paul Newcome

Happy to help. 👍️