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

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

Find more posts tagged with

Update Requests

Email Notifications

Comments

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