Automated Reminders Based on Approaching Due Date

Matthew Pickens

Hello Community,

I am attempting to produce an "if/then" statement within a column of my project management smartsheet in order to create an email automation to remind the assigned user to address the aging task. Using the formula, I should be able to create conditional formatting or email automation workflows that address the column(s) populated based on my formula context.

What I am trying to do is define a few things in my formula: 1) if the project is in a state of "in progress" or "not started" (or conversely, if the project is not "complete"), 2) if the "due date" is within 10 days of today's date, and 3) if this statement is not true for the due date, do not populate the field.

I do not want to have to individually set a reminder on each row, I want to be able to drag this formula down through my entire project tracker. I have attempted the following formula that I know from working in excel with conditional formatting but I continue to get the "#INVALID OPERATION" error: =AND([Due Date]3<"",AND([Due Date]3>TODAY(),[Due Date]3<TODAY()+10))

Does anyone know if there is a better way to complete this or what I am doing wrong within my formula?

Thanks,

Matt

Matthew Pickens

UPDATE: I have designed a work around that allows me to utilize this functionality through a few sets of automations/formulas. For anyone else that might benefit from this function, here was my process:

1) Create a "Symbol" column using flags in the sheet

2) Input the following function in that column where "End Date" in my formula is the task due date within my sheet and the (+10) is the number of days out from the due date to flag this task: =IF([End Date]2 < TODAY(+10), 1, 0)

3) This function will activate the flag and color it red whenever the "End Date" is less than 10 days from today

4) Now, I still want to filter out any tasks that may fall under that IF statement but are already complete or have been put on hold. These do not need to generate alerts.

5) Create an automation workflow for "When rows are changed" using the symbol column you just created and defined with "Changes to" a red "Flagged" icon. Add in a condition of a "Status" that "is not one of" a "Complete" or "On Hold" designation. Finally, fill out the "Alert someone" field with "Send to contacts in a cell" and define your "Assigned To" (or similar column) for defining a task/project owner. You can further define that automation to be a custom message as well instead of the automated message Smartsheet creates.

I hope some of you can find this helpful! It was a big addition to our project tracking by being proactive to the task owner and alerting them when items are approaching due date and need updating on current status. It also made it easier to ensure at end of week, I could review a report with the most up to date details on where each project/task stood, keeping the owners accountable to their tasks.

All the best,

Matt

Matthew Pickens

UPDATE: I have designed a work around that allows me to utilize this functionality through a few sets of automations/formulas. For anyone else that might benefit from this function, here was my process:

1) Create a "Symbol" column using flags in the sheet

2) Input the following function in that column where "End Date" in my formula is the task due date within my sheet and the (+10) is the number of days out from the due date to flag this task: =IF([End Date]2 < TODAY(+10), 1, 0)

3) This function will activate the flag and color it red whenever the "End Date" is less than 10 days from today

4) Now, I still want to filter out any tasks that may fall under that IF statement but are already complete or have been put on hold. These do not need to generate alerts.

5) Create an automation workflow for "When rows are changed" using the symbol column you just created and defined with "Changes to" a red "Flagged" icon. Add in a condition of a "Status" that "is not one of" a "Complete" or "On Hold" designation. Finally, fill out the "Alert someone" field with "Send to contacts in a cell" and define your "Assigned To" (or similar column) for defining a task/project owner. You can further define that automation to be a custom message as well instead of the automated message Smartsheet creates.

I hope some of you can find this helpful! It was a big addition to our project tracking by being proactive to the task owner and alerting them when items are approaching due date and need updating on current status. It also made it easier to ensure at end of week, I could review a report with the most up to date details on where each project/task stood, keeping the owners accountable to their tasks.

All the best,

Matt

Daniel Gill 97

Hi Matt, thanks for this. Quick question, I'm not very experienced with Smartsheet but, my status column shows 1s and 0s. How to I have it show Yes +10 days, Hold 10 - 1 Days, No -1 + days. Thanks! 😁