Organize information

Design a project status and overdue task tracker

Track ownership, deadlines, dependencies, blockers, and overdue work with explicit status rules

7 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Projects and review workflowStatus and deadline rulesRequired task information

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design an Excel project status and overdue task tracker.

Projects, users, update cadence, and review decisions:
[workflow]

Status, completion, overdue, blocked, and priority rules:
[statusRules]

Required task fields, dependencies, milestones, and reporting needs:
[fields]

Define one row's grain, stable keys, controlled lists, required dates, and who owns each update. Keep user-entered status separate from formula-derived health. Return table layouts, validation rules, formulas for overdue and due-soon work, dependency and blocker handling, project-level summaries, conditional formatting, and a review checklist. Explain how to preserve history when dates or status change. Do not infer progress from elapsed time alone or mark a task late after it is completed.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Projects and review workflow: A six-person operations team manages five concurrent projects. Owners update tasks each Tuesday; the manager reviews overdue work, milestones, and blockers on Wednesday. Projects stay in one workbook.
Status and deadline rules: Statuses are Not started, In progress, Blocked, Done, and Cancelled. Done requires CompletedDate. A task is overdue after DueDate unless Done or Cancelled. Due soon means within 3 calendar days. Priority is High, Medium, or Low. Blocked tasks require a reason and next review date.
Required task information: ProjectID, TaskID, Task, Owner, Priority, StartDate, DueDate, Status, CompletedDate, DependencyTaskID, IsMilestone, BlockerReason, NextReviewDate, LastUpdated, and notes. Need owner and project summaries plus a weekly history.

Example output

Create a Tasks Excel Table with the requested columns and a unique TaskID that never changes when a task moves. Use controlled lists for Owner, Priority, Status, and IsMilestone. Validate that Done requires CompletedDate, Blocked requires BlockerReason and NextReviewDate, and DependencyTaskID exists in Tasks. Keep Status user-entered; add a formula-derived Health column.

Health logic: if Status is Done or Cancelled, return Closed; if DueDate is blank, return Review; if DueDate<TODAY(), return Overdue; if DueDate<=TODAY()+3, return Due soon; if Status is Blocked, return Blocked; otherwise return On track. Put Blocked before date tests instead if the team wants blockers to dominate. Add DaysPastDue only for open overdue work and do not calculate percent complete from calendar time.

Build PivotTables by ProjectID and Owner showing open, overdue, due-soon, blocked, and completed counts; list overdue high-priority tasks rather than hiding them in totals. Add a Milestones view sorted by DueDate. Each Tuesday, append ProjectID, TaskID, Status, DueDate, Health, Owner, and SnapshotDate to a History table so deadline changes remain visible. Wednesday checks: duplicate IDs, missing owners or dates, invalid dependencies, stale LastUpdated, blocked items without review dates, and summary counts reconciled to filtered task rows.

Why this works

1
Stable task IDs and separate derived health fields make changed deadlines and statuses auditable.
2
Explicit completion and overdue rules prevent false alerts and inconsistent reporting across projects.

Check the result

Does every task have one stable ID, accountable owner, and unambiguous due date rule?
Are completed tasks excluded from overdue and due-soon flags?
Can project summaries be reconciled to the underlying task rows?

More ways to explore