IF THEN Reminders

Chris Mazuran
Chris Mazuran ✭✭
edited 12/09/19 in Smartsheet Basics

I want to set up a reminder for the unfinished tasks on a sheet. When I say "unfinshed" I mean those tasks that have not been marked done or have a 100% in the completed column. Presently, it looks like a reminder will fire regardless of task completion and that simply makes the reminder feature worthless. Is there a way to create an IF/THEN statement for a reminder? 

If not, please add this functionality!

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    We've completed this by creating a separate column for setting the reminder date and using the following formula that we point to the for the reminder date. This formula puts the text "Complete if the item is marked as complete, otherwise, it puts the due date. The reminder notification won't fire if it sees the word complete... its looking for a date so this in essence stops the reminder from firing.  

    =IF(Status24="complete", "complete", End24)

    This formula assumes you are putting it on row 24 and your Status column and Due Date column are called Status and End. We set the reminder time to fire x number of days before the end date but you could also modify the reminder date by subracting a certain number of days from the end date. 

    =IF( Status24 = "complete", "Complete", End24 - 7) would subtract 7 calendar days from the end date. 

    =IF(Status24 = "Complete", "Complete", Workday(End24, -7)) would subtract 7 business days.

    Hope that helps!  

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    To adjust for the %complete column... you could do the following. 

    =IF([% complete]24 = 1, "complete", End24)

    Percentage formatted columns are actually decimals up to one. So this is saying if the column called % complete - row 24 is 100%, then add complete, otherwise, add the End date.  

  • Nice workaround. That will work. 

  • Another great workaround. Thank you!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. You're welcome! Glad they work! You can hide the column once you create it. In our application of that priniciple I made template rows that we copy for each project... so the formula will stay attached when they create new projects. 

     

  • Hello, I know I am reviving an old thread but hoping for some direction. The formula works well, but when I go to set a reminder, the only option is to select a date - not to reference a cell. Does this workaround no longer work?

    Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @johnjrmot

    If you're setting up a time-based workflow, you can set a trigger to be based on a date in a Date column like so:

    See: Create a Time-Based Automated Workflow

    Cheers,

    Genevieve

  • @Genevieve P. Thank you! I did end up going this way, was much simpler. Thanks again!