Send reminder/update a date to be the same date the next year

Bethany Garcia

We are trying to create a sheet that tracks various dates an automated reminder should be sent on an annual basis for each of our properties. The date will vary across properties, and the reminder should be sent on the same day every year.

For example, Property A has an annual sprinkler reminder date of 8/1, and Property B has an annual sprinkler reminder date of 10/5. Every year on 8/1 we want an automated notification to go to a distribution list for Property A, and every year on 10/5 we want an automated notification to go to a distribution list for Property B.

Our preference is to avoid having to manually reset the year to the next year each time it is sent, but I can't figure out how to track just a month and date without a year, or to track a full date with a year and have that date set itself to the next year automatically once that date passes.

Any ideas?

Paul Newcome

There are a few different ways to set this up, but the easiest to manage would be to have 2 helper columns. One to store the month number and one to store the day number. then in the date column you would use something along the lines of...

=IFERROR(DATE(YEAR(TODAY()) + IF(DATE(YEAR(TODAY()), [Month Helper]@row, Day Helper]@row)< TODAY(), 1), [Month Helper]@row, [Day Helper]@row), "")

Basically what this does is output the date for the current year that the month and day falls on unless today has passed that date in which case it will output the same date for next year.

Now you can set your automations up to be triggered on this date column.

Bethany Garcia

That approach makes sense to me, but I'm getting an #INCORRECT ARGUMENT error with the formula. My helper column for the month is named Sprinkler Reminder Month, and helper column for the day is Sprinkler Reminder Day, and here is the formula I am using to calculate the date =IFERROR(DATE(YEAR(TODAY()) + IF(DATE(YEAR(TODAY()), [Sprinkler Reminder Month]@row, [Sprinkler Reminder Day]@row < TODAY(), 1), [Sprinkler Reminder Month]@row, [Sprinkler Reminder Day]@row), ""))

Any thoughts on what could be causing the INCORRECT ARGUMENT error?

Paul Newcome

It looks like you missed the closing parenthesis after the second DATE function. Here is my formula updated with your column names. The missing parenthesis is in bold.

=IFERROR(DATE(YEAR(TODAY()) + IF(DATE(YEAR(TODAY()), [Sprinkler Reminder Month]@row, [Sprinkler Reminder Day]@row) < TODAY(), 1), [Sprinkler Reminder Month]@row, [Sprinkler Reminder Day]@row), "")

Bethany Garcia

That worked, thank you so much! Now taking it up a notch...we have some assets where the reminder only needs to be sent every 5 years or 10 years. Would we be able to insert additional logic for those situations to take the year of today and add 5 or 10 to get the future year? If it can be done I can't figure out where that would be added to the formula.