Due Date And Past Due Reporting - 15 days, 15-30 days, 60-36 days etc
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
-
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").
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives