Project Tracker with Status, Overdue Flags & Progress

A project tracker shows tasks, owners, due dates, and progress so a team knows what is done and what is late. You build it in Excel and use Copilot and ChatGPT for status formulas and the timeline.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It lists tasks with owner, start, due, status, and percent complete, then flags overdue work.
• Where it is used: Teams and students managing assignments and deliveries.
• What you learn: date math, status counts, conditional formatting, and AI-prompts for charts.

Features

• Task list with owner and dates
• Status counts (Not started, In progress, Done)
• Overdue flag with red highlight
• Progress chart
• AI-written status summary

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A task list with due dates

Folder Structure

project-tracker.xlsx
│
├── Sheet: Tasks     (Task, Owner, Start, Due, Status, %)
├── Sheet: Dashboard (status counts, chart)
└── Module: ProjMacros (optional VBA)

• Tasks: the work list.
• Dashboard: the summary.
• ProjMacros: optional helpers.

Step-by-Step Development

Step 1 – Task table and status

What are we building?

A table Task with a Status dropdown.

Why do we need it?

Status drives the counts and the chart.

How do we build it?

Create the table; dropdown Not started,In progress,Done.

=COUNTA(Task[Task])

• What it does: counts tasks.
• Why needed: total tasks for the dashboard.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"How do I add a dropdown with Not started, In progress, Done in Excel?"

Step 2 – Status counts

What are we building?

A count of tasks in each status.

Why do we need it?

Shows how much is finished.

How do we build it?

=COUNTIF(Task[Status], A2)

• What it does: counts tasks where Status matches A2.
• Why needed: each status gets a number.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write COUNTIF that counts tasks where Status equals A2."

Step 3 – Overdue flag

What are we building?

A formula that marks a task overdue if not done and past due date.

Why do we need it?

Late work needs attention.

How do we build it?

=IF(AND([@Status]<>"Done", [@Due]<TODAY()), "Overdue", "OK")

• What it does: if status is not Done and due date passed, shows "Overdue".
• Important line: AND(...) checks both conditions together.
• Why needed: one glance finds late tasks.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel formula that returns Overdue if status is not Done and due date is before today, else OK."

Step 4 – Conditional formatting and chart

What are we building?

Red highlight for Overdue and a progress chart.

Why do we need it?

Color and charts speed up reading.

How we build it?

Select the flag column, Conditional Formatting > Text that Contains > "Overdue" with red fill. Then chart the status counts.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"How do I highlight cells containing the text Overdue in red using conditional formatting?"

Complete Source Code

Dashboard formulas

=COUNTA(Task[Task])
=COUNTIF(Task[Status], A2)
=IF(AND([@Status]<>"Done", [@Due]<TODAY()), "Overdue", "OK")

AI prompts

ChatGPT: "Dropdown Not started, In progress, Done."
Copilot: "COUNTIF status equals A2."
ChatGPT: "Overdue if not Done and due before today."
ChatGPT: "Conditional format text Overdue red."

Running the Project

1. Open project-tracker.xlsx.
2. Add tasks with owner, dates, and status.
3. Overdue tasks turn red; dashboard counts update.
4. Expected output: a clear view of progress and late work.

Testing

Test Case 1

Input: Task due yesterday, status "In progress". Expected Output: "Overdue". Explanation: Not done and past due.

Test Case 2

Input: Task due yesterday, status "Done". Expected Output: "OK". Explanation: Done ignores the date.

Test Case 3

Input: Three tasks: one Done, two In progress. Expected Output: Done 1, In progress 2. Explanation: COUNTIF separates by status.

Common Errors

Overdue always shows

Problem: Every task flagged. Reason: Status typed as "done" lowercase. Solution: Use the dropdown so "Done" matches exactly.

TODAY not updating

Problem: Flag stale. Reason: Workbook closed; dates are static. Solution: Reopen; TODAY recalculates on open.

Chart wrong

Problem: Counts off. Reason: Range included the header. Solution: Select only the count cells for the chart.

Improvements

• Add a Gantt bar using stacked bars
• Use Power Automate to nag owners of overdue tasks
• Connect to Microsoft Planner
• Ask ChatGPT for a weekly status email
• Add priority column

Challenge Exercises

1. Add a days-overdue formula (TODAY minus Due).
2. Write a formula for percent of tasks Done.
3. Use ChatGPT to write VBA that emails owners of overdue tasks.
4. Build a stacked bar Gantt chart from start and due dates.
5. Let ChatGPT summarize blockers from the task list.