Welcome!

IF THEN Reminders

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!

Find more posts tagged with

Comments

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

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.

Chris Mazuran

Nice workaround. That will work.

Chris Mazuran

Another great workaround. Thank you!

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.

johnjrmot

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.

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

johnjrmot

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

Sign in to join the conversation!

Quick Links

Trending discussions

Timeline Widget Filter on the Dashboard
The timeline widget seems to only allow filters to be set when you are editing the widget (see green highlight screenshot). Which is fine, but it would be more valuable to have regular users be able to adjust filters directly from the dashboard, similar to how there is an option to adjust the timescale. Am I missing…
“File Creation Restriction”
Hello There , before smartsheet switched to the new license type, the users to whom I gave editing permissions could not share the page unless I granted them permission. In addition, users did not have the ability to create pages. This way, as the admin, I was aware of all the pages created in the company and could…
construction cost estimate for installations
Can someone help me and give me an idea on how to create a cost estimate for installations, with a total for each chapter by groups, subgroups and types of work, but at the bottom of each chapter. https://us.v-cdn.net/6031209/uploads/0OSH46KSC2LH/cost-estimate.xlsx Thanks