Automation based on date

Jessica Schrank

Hi, I'm looking for a formula that can be used to automate sending out a reminder based on a certain date for every X number of years. For example, I have an "invoice due" date column, and then a column next to it saying "number of years." That number of years can be every (1) year, every year for two years, every year for three years, etc.

If I wanted to insert a formula saying send reminder when invoice due column reaches that date, as well as for two years after, how would I go about that, if the number of years column is different for each row? 

Andrée Starå

@Jessica Kahlenberg

You're more than welcome!

It's because the Invoice send date is empty.

Try something like this.

=IF([Invoice send date]@row="","", DATE(YEAR([Invoice send date]@row) + [Number of years in effect]@row, MONTH([Invoice send date]@row), DAY([Invoice send date]@row)

Did that work?

Andrée Starå

Hi @Jessica Kahlenberg

One way to structure it could be something like this. (I'd recommend adding so-called helper columns for each year, year 1, year 2. . .)

=DATE(YEAR([Invoice Due]@row) + [Number of Years]@row; MONTH([Invoice Due]@row); DAY([Invoice Due]@row))

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jessica Schrank

@Andrée Starå thank you so much for your response.

Unfortunately when I put that in, it says "#unparseable". Here is what I put in: =DATE(YEAR([Invoice send date]@row) + [Number of years in effect]@row; MONTH([Invoice send date]@row); DAY([Invoice send date]@row))

Andrée Starå

@Jessica Kahlenberg

Happy to help!

Try this.

=DATE(YEAR([Invoice send date]@row) + [Number of years in effect]@row, MONTH([Invoice send date]@row), DAY([Invoice send date]@row))

Did it work?

Jessica Schrank

@Andrée Starå thank you! that worked for the first row..looks like the second row says "invalid data type" when i add the same formula - do you know why?

Andrée Starå

@Jessica Kahlenberg

You're more than welcome!

It's because the Invoice send date is empty.

Try something like this.

=IF([Invoice send date]@row="","", DATE(YEAR([Invoice send date]@row) + [Number of years in effect]@row, MONTH([Invoice send date]@row), DAY([Invoice send date]@row)

Did that work?

Jessica Schrank

@Andrée Starå thank you!

Andrée Starå

@Jessica Kahlenberg

I'm always happy to help!