Due Date And Past Due Reporting - 15 days, 15-30 days, 60-36 days etc

Amanda LB
Amanda LB
edited 01/28/25 in Smartsheet Basics

I'd like to create a corrective action tracker assigning due dates top specific tasks, and have a column with a check box defining whether that task is 15 days or less past the due date, 15-30 days, 30 - 60 days, and 60 + days past due. Then I would like to create a report and publish a graph widget on my dashboard. (I stole the widget from another dashboard I found.

I would also like to e-mail the supervisor and observer a week prior to the due date.

Answers

  • L J
    L J ✭✭

    To get you started, here is a formula to calculate the Due Date Status in a single Text/Number column:

    =IF(TODAY() - [Due Date]@row < 0, "Not Yet Due", IF(TODAY() - [Due Date]@row = 0, "Due Today", IF(TODAY() - [Due Date]@row < 15, "<15 Days Past Due", IF(TODAY() - [Due Date]@row <= 30, "15-30 Days Past Due", IF(TODAY() - [Due Date]@row <= 60, "31-60 Days Past Due", ">60 Days Past Due")))))

    To update your checkbox columns, you'd need to use something like this (15-30 days as the example):
    =IF(AND(TODAY() - [Due Date]@row >= 15, TODAY() - [Due Date]@row <= 30), 1, 0)

    The example chart looks at the Due Dates in the opposite way - things that are COMING due, vs. things are past due.

    To generate a chart, you could calculate the number of each item in the Sheet Summary (see screenshot) then create a Sheet Summary Report to base your chart on:

    Create one item in the Sheet Summary for each:

    Create a new Sheet Summary Report based on the specific items in your Sheet Summary:

    Then, lastly, on your Dashboard, add a Chart widget that used the Sheet Summary report as its source:

    You can change how the chart looks using the options in the Chart Widget panel, e.g.:

    To send an email a week before the due date, you can use an Automation set-up for your sheet ("Alert someone when specified criteria are met").