Setting up a weekly automation to alert someone in 30, 60 and 90 days of a date

Mike.Jennings56876
Mike.Jennings56876 ✭✭
inSmartsheet Basics

I have a sheet that tracks employees certifications. I'd like to setup a single automation that runs weekly to alert the employee when the expiration date is within 180, 90, 60 and 30 days away. I'd prefer that the 180 day alert only be sent monthly until the 90 day date is hit, then weekly. I'd like the message sent to reflect the time left before the certification expires or at least the 180, 90, 60 or 30 day range.

I have a basic automation built that is working but it's triggering in the appropriate date ranges or triggering for all of them.

· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest a helper column that uses a nested IF to output which alert should be sent (or blank if no alert is to be sent). Then you can set your automation up to run weekly and use a condition of this helper column being in that particular range.

    =IF([Expiration Date]<= TODAY(30), "30 Days", IF([Expiration Date]@row<= TODAY(60), "60 Days", IF([Expiration Date]@row<= TODAY(90), "90 Days", IF([Expiration Date]@row<= TODAY(180), "180 Days"))))

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest a helper column that uses a nested IF to output which alert should be sent (or blank if no alert is to be sent). Then you can set your automation up to run weekly and use a condition of this helper column being in that particular range.

    =IF([Expiration Date]<= TODAY(30), "30 Days", IF([Expiration Date]@row<= TODAY(60), "60 Days", IF([Expiration Date]@row<= TODAY(90), "90 Days", IF([Expiration Date]@row<= TODAY(180), "180 Days"))))

    10xViz.com

    · Share on Facebook Share on Twitter
  • Mike.Jennings56876
    Mike.Jennings56876 ✭✭

    Thanks@Paul Newcome, that worked great. How would I handle a blank expiration date? The sheet tracks achieved certs and planned certs, the latter don't have expiration dates yet.

    · Share on Facebook Share on Twitter

Categories

I would suggest a helper column that uses a nested IF to output which alert should be sent (or blank if no alert is to be sent). Then you can set your automation up to run weekly and use a condition of this helper column being in that particular range.<\/p>

=IF([Expiration Date]<= TODAY(30), \"30 Days\", IF([Expiration Date]@row<= TODAY(60), \"60 Days\", IF([Expiration Date]@row<= TODAY(90), \"90 Days\", IF([Expiration Date]@row<= TODAY(180), \"180 Days\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":110944,"type":"question","name":"Alerts","excerpt":"Hi there, I want to use the trigger and alert to notify someone in the next row when the task above is complete. E.g., if the Status column is changed to Complete in row 3, I want an alert to be sent to the contact in the Assigned To column in row 4. Is that possible? Megan","snippet":"Hi there, I want to use the trigger and alert to notify someone in the next row when the task above is complete. E.g., if the Status column is changed to Complete in row 3, I want…","categoryID":321,"dateInserted":"2023-09-28T18:30:24+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T17:38:02+00:00","insertUserID":167668,"insertUser":{"userID":167668,"name":"mmburns","url":"https:\/\/community.smartsheet.com\/profile\/mmburns","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T17:38:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167567,"lastUser":{"userID":167567,"name":"dschrimpsher","title":"Enterprise Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/dschrimpsher","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!CSDrwr4e8_o!pFFr6eYKo4I!_leR1jGucXU","dateLastActive":"2023-09-29T18:19:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3391936706,"url":"https:\/\/community.smartsheet.com\/discussion\/110944\/alerts","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110944\/alerts","format":"Rich","lastPost":{"discussionID":110944,"commentID":397720,"name":"Re: Alerts","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397720#Comment_397720","dateInserted":"2023-09-29T17:38:02+00:00","insertUserID":167567,"insertUser":{"userID":167567,"name":"dschrimpsher","title":"Enterprise Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/dschrimpsher","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!CSDrwr4e8_o!pFFr6eYKo4I!_leR1jGucXU","dateLastActive":"2023-09-29T18:19:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-29T17:20:41+00:00","dateAnswered":"2023-09-28T22:57:55+00:00","acceptedAnswers":[{"commentID":397558,"body":"

Hi,<\/p>

You can use checkbox column to trigger the person assigned to in row 4. In row 4 checkbox, use the below formula:<\/p>

=IF(Status3 = \"Complete\", true, false)<\/p>

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":110885,"type":"question","name":"MAX COLLECT for dates in a row","excerpt":"I have a certification that requires a few lessons to be fully certified. Each lesson has a completion date in it's own column and a date column for when they become fully certified. So, if there if one training is missing a completion date they are not considered fully certified. My need is to be able to collect the max…","snippet":"I have a certification that requires a few lessons to be fully certified. Each lesson has a completion date in it's own column and a date column for when they become fully…","categoryID":321,"dateInserted":"2023-09-27T20:11:08+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T01:52:58+00:00","insertUserID":120097,"insertUser":{"userID":120097,"name":"Ronald Anderson","url":"https:\/\/community.smartsheet.com\/profile\/Ronald%20Anderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/TW6ZN66U6IZH\/nA9VM54512X3E.jpg","dateLastActive":"2023-10-02T02:21:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-10-02T01:33:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":49,"score":null,"hot":3391713246,"url":"https:\/\/community.smartsheet.com\/discussion\/110885\/max-collect-for-dates-in-a-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110885\/max-collect-for-dates-in-a-row","format":"Rich","lastPost":{"discussionID":110885,"commentID":397353,"name":"Re: MAX COLLECT for dates in a row","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397353#Comment_397353","dateInserted":"2023-09-28T01:52:58+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-10-02T01:33:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-28T03:03:43+00:00","dateAnswered":"2023-09-28T01:52:58+00:00","acceptedAnswers":[{"commentID":397353,"body":"

You can skip the collect and just use MAX():<\/p>

=IF([Fully Certified]@row = \"Yes\", MAX([Train Date - Blower]@row, [Train Date - Broom]@row, [Train Date - Chem]@row), \"\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":5116,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Setting up a weekly automation to alert someone in 30, 60 and 90 days of a date

    I have a sheet that tracks employees certifications. I'd like to setup a single automation that runs weekly to alert the employee when the expiration date is within 180, 90, 60 and 30 days away. I'd prefer that the 180 day alert only be sent monthly until the 90 day date is hit, then weekly. I'd like the message sent to…

  • Alerts

    Hi there, I want to use the trigger and alert to notify someone in the next row when the task above is complete. E.g., if the Status column is changed to Complete in row 3, I want an alert to be sent to the contact in the Assigned To column in row 4. Is that possible? Megan

  • MAX COLLECT for dates in a row

    我有一个认证需要few lessons to be fully certified. Each lesson has a completion date in it's own column and a date column for when they become fully certified. So, if there if one training is missing a completion date they are not considered fully certified. My need is to be able to collect the max…

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video