Automation based on date
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?
Best Answer
-
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?
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.
Answers
-
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!
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.
-
@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))
-
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.
-
@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?
-
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?
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.
-
@Andrée Starå thank you!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!