Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Alert User in Row if Date has Past Due and not Complete
I would like to send an alert to the person "assinged" to the appropriate row the day after a "Due Date" IF a "Status" column has/doesn't have a value(s).
For example, "Send 'Assigned To' and email reminder 1 day after 'Due Date' IF 'Status' does not equal 'Completed; Canceled.'"
I want to target these communications to only occur when the person has missed a deadline and aviod overcommunicating. I don't want these alerts to turn into emails that occur so frequently that they inevitably get ignored.
Cheers,
Andy
Comments
-
You can set a reminder to remind the 'Assigned to' at set points with an update of tasks or '# days before' I havent managed to add in the IF 'Status' does not equal 'Completed; Canceled.'" but i have added a comment into the email to remnind people to check this.
I will be interested to see if there is another solution as i have changed to a single weekly task status update.
问候
James
-
Someone else has solved this in another discussion:
This can be done!
Here are the steps:
1. Add a date column to your sheet
2. Add a formula to the new date column that will show the corresponding Due Date IF the corresponding Status is not Completed or Canceled
3. Set reminders based on new date column
Here is the formula you can use: =IF(Status1 = "Complete", "", IF(Status1 = "Canceled", "", [Due Date]1))
- See more at:https://community.smartsheet.com/discussion/past-due-reporting#comment-2834
Categories
=SUMIF([Color]:[Color], @cell = [Color]@row, [# of Hours]:[# of Hours])<\/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":108490,"type":"question","name":"Assistance creating formula to reference status of previous task","excerpt":"My team currently uses Smartsheet to manage the various projects we have across our locations. Within each location is a project line, and then nested within this is the project schedule and then nest within that are the various tasks associated with completing the project. Each task is assigned to a user responsible for…","snippet":"My team currently uses Smartsheet to manage the various projects we have across our locations. Within each location is a project line, and then nested within this is the project…","categoryID":322,"dateInserted":"2023-08-03T17:41:08+00:00","dateUpdated":"2023-08-03T17:42:31+00:00","dateLastComment":"2023-08-03T18:33:40+00:00","insertUserID":164514,"insertUser":{"userID":164514,"name":"dmarkson","url":"https:\/\/community.smartsheet.com\/profile\/dmarkson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jRMoI4sIzzo!pPdpKXfxpro!IkFwRkvbxHr","dateLastActive":"2023-08-03T21:11:39+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-03T21:18:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":27,"score":null,"hot":3382172688,"url":"https:\/\/community.smartsheet.com\/discussion\/108490\/assistance-creating-formula-to-reference-status-of-previous-task","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108490\/assistance-creating-formula-to-reference-status-of-previous-task","format":"Rich","lastPost":{"discussionID":108490,"commentID":388774,"name":"Re: Assistance creating formula to reference status of previous task","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388774#Comment_388774","dateInserted":"2023-08-03T18:33:40+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-03T21:18:42+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I7DOLV7N4VEN\/project-tracker.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Project Tracker.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T21:11:37+00:00","dateAnswered":"2023-08-03T18:33:40+00:00","acceptedAnswers":[{"commentID":388774,"body":"
@dmarkson<\/a> <\/p> Here is a community thread that may help.<\/p>
