# Automation based on date

Options

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?

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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

• ✭✭✭✭✭✭
Options

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?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

@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?

• Options

@Andrée Starå thank you!

• ✭✭✭✭✭✭
Options

I'm always happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!