Alert to Row owner when Task Not complete and Due Date past

Tim Meeks

I posted this as a reply to another post, but thought I'd start as a new discusssion.

 

https://community.smartsheet.com/discussion/past-due-reporting

 

I want to send an Alert to a Task Row Owner when  the Task Due Date is past and the Status is NOT complete.  If this condition is true, I got the idea from the above post to populate a cell with Today's date.

 

Then i'll set up an alert to be sent if the Alert Date = TODAY.   In this new column called Alert Date, I made it a Date property type and have added the following formula.  

 

The logic reads: If Today is older(greater) than the Due Date and the Status is NOT completed, then populate the cell with Todays' date, else blank.

 

=IF(AND(TODAY() > [Due Date]3, NOT(Status3) = "Completed"), TODAY(), "")   

 

However, I'm getting a #MISSING OR INVALID PARAMETERS error.  I've changed the location of the parenthesis several times and still can't get it work.   Any help is appeciated. 

 

And if there is a better way to do, please let me know.

 

thanks,

Tim

Travis

I looked through the linked discussion and it sounds like you were able to get an answer for this, correct?

 

Here is the comment URL: https://community.smartsheet.com/discussion/past-due-reporting#comment-3025

 

The comment by Bobby Andres:

 

"Tim, move the = "completed" inside the parenthesis like this:   

 

=IF(AND(TODAY() > [Due Date]3, NOT(Status3 = "Completed")), TODAY(), "")  

 

Also, remember reminders have to be set at least 1 day in the future in order for them to send. This means if you set a reminder for TODAY(), it will not send. You could do TODAY(1) which is today + 1 (tomorrow) or you could tell the reminder to send one day after the TODAY() date."

Sophie Waters

Hi,

 

I am also wanting to set up alerts to any tasks that have not been completed by the end date on any smartsheet and not marked as 100% in the %Complete column - can I do this?

 

I am pretty new to Smartsheet so I apologise if this has already been answered.

 

At the moment I have a start and end date for each stage of my project, with assigned to as a contact list. I also have a %Complete column that everyone is to update with their status in percentage. If this isn't at 100% on the end date listed I want the assigned to person to be sent an email reminder asking for them to update with this status.

 

Is this possible?

 

Many thanks,

Sophie

 

 

Travis

Hi Sophie - yes this is possible!

 

Check out this blog post on How to Create Conditional Reminders for more information.

Sophie Waters

Ah, thank you so much - this solves a big issue for me!

I have set up a test entry to see this working in all it's glory.

 

Thanks again! 

Shaine Greenwood

Hi All,

 

I wanted to mention that we're actively working on this feature. If you have ten minutes to spare, we'd love to get your participation in a usability study we're running.

 

For more information, see this annoncement:

https://community.smartsheet.com/announcement/weigh-smartsheets-next-strategic-feature