Dashboard Mode - Why date calculations drive business logic
Business Question
How can we use date calculations to track project deadlines and measure overdue tasks?
0
0
Why date calculations drive business logic in Google Sheets - Dashboard Impact
Sample Data
| Task | Start Date | Due Date | Completion Date |
|---|---|---|---|
| Design | 2024-06-01 | 2024-06-10 | 2024-06-09 |
| Development | 2024-06-05 | 2024-06-20 | 2024-06-22 |
| Testing | 2024-06-15 | 2024-06-25 | |
| Deployment | 2024-06-26 | 2024-06-30 | |
| Review | 2024-07-01 | 2024-07-05 |
Dashboard Components
- KPI Card: Total Tasks
Formula:=COUNTA(A2:A6)
Shows total number of tasks (5) - KPI Card: Overdue Tasks
Formula:=COUNTIFS(D2:D6, ">&" & C2:C6, C2:C6, "<" & TODAY()) + COUNTIFS(D2:D6, "", C2:C6, "<" & TODAY())
Counts tasks completed late or not completed past due date (2) - Table: Days Overdue
Formula in E2:=IF(D2="", MAX(0, TODAY()-C2), MAX(0, D2-C2))
Calculates how many days a task is overdue (0 if on time or early) - Chart: Overdue Tasks by Days
Uses Days Overdue column to show distribution of overdue days visually
Dashboard Layout
+----------------------+----------------------+ | Total Tasks (KPI) | Overdue Tasks (KPI) | +----------------------+----------------------+ | Overdue Tasks by Days Chart | +---------------------------------------------------------------------+ | Tasks with Days Overdue Table | +---------------------------------------------------------------------+
Interactivity
Adding a date filter (e.g., filter tasks by Start Date or Due Date range) updates the KPI cards, the overdue days table, and the chart to reflect only tasks within the selected date range.
Self Check
If you add a filter to show only tasks with Due Date after 2024-06-15, which components update and what changes?
- KPI Cards update to count only tasks due after 2024-06-15.
- Overdue Tasks count changes based on filtered tasks.
- Days Overdue table shows only filtered tasks.
- Chart updates to show overdue distribution for filtered tasks.
Key Result
Dashboard shows total tasks, counts overdue tasks, calculates days overdue, and visualizes overdue distribution using date calculations.