Due Date alert

Bob Bobo

Is there a way to alert a user that he has a due date approaching either through the native app or slack?

bob

Kelly Moore

Hello Bob

I can't speak to Slack but you may have a few options in Smartsheet. If you wanted a weekly, monthly, etc notice to go out regardless of the exact due date, you can do this in Automation using 'Alert Someone' and trigger 'when a Date is reached'. Pick a start date. In the Frequency, select Custom and you'll have options to schedule a reoccurring task.

Alternatively, with a helper column and the REMIND SOMEONE automation routine, you can specify number of days prior to due date, for example 14 days prior. The helper column must be a DATE column. Using the example of 14 days prior, add this formula in the helper column:

=[Due Date]@row - 14

This will populate the helper column with a date that is 14 days prior to the Due Date. If you wanted Workdays only so that 2 weeks was really 10 working days, use this formula in the helper column:

=Workday([Due Date]@row, -10)

My 14 days (or 10 days working days) was only an example - you can make it whatever you like. Also, make sure to change the [Due Date] column name to whatever your actual Due Date column name is.

With the helper column set, create a REMINDER automation routine. Trigger this routine off 'When a Date is Reached' and select the Helper Date column you just created.

If you are also tracking task completion in your sheet, consider adding a condition to both of the above routines to only send if the task was not completed - so not to bother someone that had already completed their task.

I hope this helps,

Kelly

Thiara Perez

@Kelly Moore Hi Kelly, this is a bit late but this answer may be exactly what I was looking for. I just have a question so I can understand the differences between these options.

1- picture below: this how I initially had reminder automation saved, but it was sending reminders daily. Why? I thought the conditions would make it so that the reminder only goes out IF the date is in the next 30 days, even if the automation runs daily.

2- picture below: this is how I have it set up now: how is this different from the first one? should I add a condition that says "where due date is in the next 30 days" or is how it is now enough?

And lastly, how can I make it so that the reminder received includes ONLY the row that meets the criteria? For example, Dani has 10 rows of tasks assigned, but only 2 are due in the next 30 days. I'd like for the email to include only those 2 tasks, not all 10 of them. Is this possible?

I have it set up as above hoping that I can do this without using helper columns. Unless, you recommend using helper columns is the best approach.

Thank you SO much!

Thiara.

Kelly Moore

Hey @Thiara Perez

The difference between my workflow and yours is "in the next 30 days". This doesn't mean in 30 days it means anytime in the next 30 days. Day 5 is in the next 30 days. Day 29 is in the next 30 days, etc. Because I like the flexibility of setting the number of days for exactly what I want it to be, I typically have a helper column that is either a duration, so it is counting days from one date to another, or if that duration is unchanging, I might convert it to a checkbox. Either option allows me to make my conditions specific enough that only the rows that exactly match the conditions are triggered.

Your picture #2 says to run the workflow on the row once where it is exactly 30 d before the due date and only if Completion is blank and response is blank. I think this is the one you want to use.

Does this explain the difference?

Kelly

Thiara Perez

Hi @Kelly Moore wow, I get it now, this is SO helpful, thank you so much! I am new to Smartsheet so I greatly appreciate this. I doubt my reminders will change so I think the last picture should work for me.

Now, what about the email reminder including *just* the rows that meet the 30-day criteria? If person A has 5 tasks but only 2 are due in 30 days, is it possible to add just those 2 tasks to the body of the reminder email so they can see exactly the ones due in 30 days?

Thiara Perez

Hi @Kelly Moore - I am back here because apparently, I cannot for the life of me, get this due date reminder thing to work. I have been searching for hours and can't find a solution.

I have the workflow below. However, when I ran it to test it out, the alert included ALL the rows that meet the conditions, regardless of when the due date is. Some rows had due dates in the past or even in 2025, but still included in the email alert --- Why? Why is it picking up everything instead of only the rows which due date is in exactly 2 weeks? Am I interpreting the workflow wrong?

Then I tried adding a third condition (pic below): "when due date is in the next 15 days" - this worked better as the alert excluded all those other rows with due dates in the past or in 2025. However, like you said before, it also picked up rows with due dates anywhere between now and the next 2 weeks.

So now I am confused because I don't understand the purpose of the "2 weeks before" selection in the Trigger box. Also, we need the conditions to include a date option for "exactly in" instead of just "in the next". I even tried your suggestion of using a helper column to calculate "due date -15" (to give me a date 15 days before the due date) and then just run the workflow for "when that date is reached". But again, ALL the rows where included in the email alert even if the date in the helper column is not today. I am at a loss. Do you have any idea of what I could be doing wrong?

Kelly Moore

Hey @Thiara Perez

Sorry this hasn't been working for you.

I can think of two options. If you have the helper column, in addition to adding to adding that as the trigger you can also add it as a condition. I sometimes have a trigger of 'when the helper column changes to any value' then I have the specifics, which include the exact countdown value I'm looking for, in the condition box. You can then add different countdown flows within the same automation (using the 'Otherwise')

If you keep the trigger for the canned 2weeks before, you could add the helper column as a condition, which should fix the extra rows (famous last words), and consider add a condition of when due date is not in the past.

Shout out if one of these don't fix it and we'll come up with something else.

Kelly

www.linkedin.com/in/kelly-moore-1659592a

Thiara Perez

@Kelly Moore thank you so much for all your help. So here is what I was able to do to fix the issue. I used your suggestion of using the condition "is not in the next" so it can drop all those days in between. I also found this suggestion in another thread.

I also realized, when I was using the helper column to calculate the 2-week before date and then run the workflow "when that date is reached", I was only getting all the rows because I was testing it using "run now", which for some reason picked up all the rows, not sure why? However, I did not want to use this method because then I'd need like 4 or 5 helper columns for each reminder type I send (upcoming and late). At least I fixed it for now, but still can't understand why the canned "2 weeks before" in the trigger box did not work for me. I appreciate your help. Thank you.