Past Due Reporting

Mark Chapman

Is there a method to have a report that generates past due tasks?

On our task management sheet, I have a ‘due date’ and ‘date completed’. Once a task is finished, my team members will input the date completed. If the date completed extends past the due date, I would like a report to be generated and available to the Management team.

JamesR

Create a Flag field that is calculated to turn Red if the Completed d ate is greater than the Due date.

Reprort on the Flag being Red

David Owens

Or, related thought, create an overdue days field to calculate how many days a task is past due then filter and sort as needed to create the report.

Andy Klatt

Or, better yet, is there a way that an alert can be emailed to the "Person Responible" column when the item is past due?

John Hammond

Andy,  set a reminder to email the person assigned to the task on (or after) the due date. You can set up a single sheet level reminder which will set a reminder for all the tasks in your sheet and will look to a contact list and date column to tell it when to send the email and who to send it to.

 

Reminders: http://help.smartsheet.com/customer/portal/articles/542913-using-reminders

Andy Klatt

Thanks John.

However, I think I omitted a desired parameter. I would only like to send the alert to the person for the appropriate row IF a "Status" column has/doesn't have a value(s).

 

For example, "Send 'Assigned To' and email reminder 1 day after 'Due Date' IF 'Status' does not equal 'Completed; Canceled.'"

 

I want to target these communications to only occur when the person has missed a deadline and aviod overcommunicating.  I don't want these alerts to turn into emails that occur so frequently that they inevitably get ignored.

 

Cheers,

Andy

John Hammond

This can be done!

 

Here are the steps:

 

1. Add a date column to your sheet

2. Add a formula to the new date column that will show the corresponding Due Date IF the corresponding Status is not Completed or Canceled

3. Set reminders based on new date column 

 

Here is the formula you can use:

 

=IF(Status1 = "Complete", "", IF(Status1 = "Canceled", "", [Due Date]1))

Andy Klatt

Holy schnikies! This worked like a charm.

 

Thanks so much. I think this will be great tool to keep people on task.

 

Thanks.

Tim Meeks

John and Andy,

 

I'm trying to do something similar.  I want to send an Alert to a Task Owner when  the Due Date is past and the Status is NOT complete.  If this condition is true, I want the cell to populate with Today's date. Then i'll set up an alert to be sent if the Alert Date = TODAY().

 

I added a new column called Alert Date and made it a Date property type and have added the following formula.

 

=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.

 

thanks,

Tim

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. 

Tim Meeks

Bobby,

 

That worked!  i knew I had a parenthesis off somewhere. Also, thanks for the reminder on the Reminders. 

I plan to set it up to be TODAY(1) and will have the Reminder sent 1 day before TODAY(1).

Hopefully, that will work.

 

Tim

 

Tim

Travis

Just to chime in here... Tim, reminders need to be set at least one day in the future. If you set a reminder to send 1 day before TODAY(1), that is still "today" and it will not send. Part of this is because the system that sends reminders will send them between 12am - 6am. For example, if you are in a sheet at 8am and set a reminder to send "today", it will not send because the system already sent out all the reminders for the day. 

 

How about this for an alternative. Rather than checking the Due Date AND the Status, check ONLY the Status and show the Due Date IF the Status is NOT "Completed" and show a blank value otherwise. Then set a reminder to send on the date, which will only send if there is a date in the cell (Not Completed). 

 

Example: 

 

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

 

This will show the Due Date, unless the task is marked as Completed. If you have a reminder set for this column, then the reminder will send on the Due Date, unless the task is marked as Completed.

Matt Pawlikowski

Shouldn't filters be a basic function of sheet reminders? I was surprised to find that adding a 'status is not complete' filter is not part of the basic tool set. While your solution above may work, you're placing the onus on the customer to fix a product oversight. I don't have the time to implement this across all of our sheets and verify it is working. I also don't like getting reminder emails on the due date for tasks that were already completed.

steven.reed14881

Matt,

I agree 100% with your statement. Your approach of including the additional layers of logic into the alerts makes total sense. To have to insert complex into individual sheets indeed incorrectly shifts the responsibility to users rather than offering product functionality that seems fundamental.

I love your product, but come on Smartsheet!

 

Steve Reed

steven.reed14881

I am trying to do the exact same thing. I came up with the exact same concept (using a slightly different formula), however, I did not add the one day to today. That may help, but I have a concern with this solution that I am worried about.

My question is whether the value in this field will recalculate automatically overnight prior to the alert being sent out. When I checked on my test scenario the value did not update on its own. It reflected the same value that it had the previous day until i went into the sheet and saved that sheet.

I am going to test it again tonight, and that may answer my question, but if it doesn't work properly, I will be back for more help.

 

Steve

Dan Engerer

Honestly, this is the dumbest thing I've ever seen for a supposedly very successful and popular program. You can't even notify your users of a late task without digging down and making extra code and columns like some sort of excel geek. Really? Honestly super disappointing.