A Project Tracker That Flags Slipping Tasks Before They're Late

A task isn't a problem when it becomes late. It's a problem weeks earlier, when it stops keeping up. This tracker compares how much of each task is finished against how much of its window has already gone, and flags the gap while there's still time to do something about it.

⬇ Download the Project Task Tracker (.xlsx)
Free, no email required. Works in Excel 2016 and later. Prefer Google Sheets? Open a copy in your own Drive.

The rest of this guide explains how it decides what's slipping, and why the percentage it reports isn't the one most trackers show you.

Nothing in the amber rows is late yet. Each one has used more of its time than it has delivered, which is why they are flagged now.

What it does

Sheet	What it's for
Tasks	One row per task. You fill in seven columns; Expected, the progress bar and Status calculate themselves.
Summary	Completion weighted by effort, a count by status, and a breakdown by phase and by owner.
Lists	Phases, owners, and the one setting that controls how far a task may drift before it's flagged.

Where a task should be by now

The whole design rests on one column. If a task runs from the 1st to the 31st and today is the 16th, half its window is gone — so roughly half of it ought to be done:

=IF($A2="","",
   IF($E2<=$D2, 1,
      MEDIAN(0, (TODAY()-$D2)/($E2-$D2), 1)))

The division is the honest part: elapsed days over total days. Everything around it is protection.

MEDIAN(0, x, 1) is the piece worth stealing. The middle value of zero, your number and one is always your number clamped into the range 0–1. Feed it −0.4 for a task that hasn't started and the middle value is 0. Feed it 1.8 for one long past its date and the middle value is 1.

The obvious alternative works too:

=MIN(MAX(x, 0), 1)

Same result, and arguably easier to read the first time. MEDIAN wins on being one function instead of two, and on stating the bounds in the order they appear on a number line.

⚠️ The IF($E2<=$D2, 1, ...) is not decoration. If a due date lands on or before the start date, the divisor is zero or negative and the formula returns #DIV/0! or nonsense. One mistyped date shouldn't take out the column.

The four states, in the order they're tested

=IF($A2="","",
   IF($G2>=1, "Done",
      IF(TODAY()>$E2, "Overdue",
         IF($G2<$H2-Lists!$E$1, "Behind", "On track"))))

Read it as a sequence of questions, each asked only if the previous answer was no.

1. Finished? Then nothing else matters. A task completed a day after its deadline is done, not overdue — chasing it would be noise.
2. Past its date and not finished? Overdue. This one is factual, so it outranks any estimate.
3. Further behind than the tolerance allows? Behind.
4. Otherwise, on track.

Get that order wrong and the tracker lies to you in a specific way: put the Behind test above the Overdue test and genuinely late work starts reporting as merely slipping. Nested IF ordering is always worst-case first.

Why there's a tolerance at all

Lists!$E$1 holds 10%. Without it, any task even fractionally behind its straight line turns amber — and real work doesn't advance in a straight line. Nothing happens for three days, then a lot happens in an afternoon.

Ten percent is loose enough to absorb that and tight enough to catch a genuine stall. Change the cell and all 120 rows re-read it; never type the number into the formulas.

A progress bar with no chart

=IF($A2="","",
   REPT("█", ROUND($G2*10,0)) & REPT("░", 10-ROUND($G2*10,0)))

REPT(text, n) repeats text n times. Forty percent gives four filled blocks, then six empty ones to pad the bar to a constant width. The two characters tile edge to edge, so what you see is a continuous bar rather than ten separate marks.

Note that ROUND appears in both halves. It has to — if one side rounded and the other truncated, the bar would be eleven characters on some rows and nine on others, and a bar that changes length stops being scannable.

This costs nothing, survives being copied into an email, and reads faster than the number beside it. Data bars via conditional formatting are prettier; this works everywhere, including in a printed page.

The percentage that's usually wrong

Most trackers report completion as finished tasks over total tasks. That number is close to meaningless, because tasks aren't the same size.

Finish nine tasks that take an hour each and leave one that takes three weeks, and counting says you're 90% done. You're not.

=SUMPRODUCT(Effort, Percent) / SUM(Effort)

SUMPRODUCT multiplies the two ranges row by row and adds the results. Each task contributes its effort multiplied by how far along it is, and dividing by total effort turns that into a weighted percentage.

The template shows both figures, side by side, deliberately. In the example project they read 51% by effort and 39% by count — and the gap between them is the information. A wide gap means your small tasks are finishing and your large ones aren't, which is the most common way a project quietly runs late.

The same pattern gives completion per phase:

=IFERROR(SUMPRODUCT((Phase=Lists!$A$2) * Effort * Percent)
         / SUMIF(Phase, Lists!$A$2, Effort), 0)

The comparison produces TRUE and FALSE, which multiply as 1 and 0, so rows in other phases fall out of the sum. IFERROR catches the empty phase — no tasks means no effort means division by zero.

One thing worth copying even if you don't use the template

Notice that the phase name isn't typed into that formula. It's Lists!$A$2 — a reference to the cell where the name lives.

Type "Build" into the Summary instead, and the day someone renames that phase to "Construction" on the Lists sheet, the Summary quietly reports zero. No error, no warning, just a row of blanks that looks like a real answer.

Point every formula at the cell holding the label, and renaming the label updates everything at once. The template does this for phases and for owners, which is why you can replace the placeholder owner names with your team's and the by-owner breakdown just works.

Building it from scratch

1. Lists sheet — phases in column A, owners in column B, tolerance in one cell.
2. Tasks sheet — Task, Phase, Owner, Start, Due, Effort, % done, Expected, Progress, Status, Notes.
3. Data validation on Phase and Owner from the Lists columns; a decimal rule between 0 and 1 on % done.
4. The MEDIAN clamp in Expected, REPT in Progress, the four-way nested IF in Status.
5. Three conditional formatting rules keyed on Status, ordered Done, Overdue, Behind, each with Stop If True.
6. Summary sheet — COUNTIF by status, SUMPRODUCT for weighted completion, the phase and owner blocks.

Around forty-five minutes. The Summary is most of it.

Does this work in Google Sheets?

Yes. MEDIAN, TODAY, REPT, ROUND, SUMPRODUCT, SUMIF, COUNTIFS and nested IF all behave identically, and the block characters render fine. Rule order is set by dragging in the conditional formatting panel rather than in a Manage Rules dialog.

When it misbehaves

Expected shows 100% on a task that hasn't started

The start and due dates are the same, or reversed. The guard returns 1 by design — a zero-length task is either done or late, never partly through.

Everything is Behind

The tolerance cell is empty, so it reads as zero and any task even slightly off its straight line gets flagged. Put a value back.

The progress bar is a row of hashes

The column is too narrow. Excel shows #### rather than clipping. Widen it.

The bar shows squares or question marks

The font doesn't carry the block characters. Set that column to Arial, Calibri or Segoe UI. If it still fails, REPT("|",n) works in any font.

A phase shows zero tasks but you can see them

The phase text on the Tasks sheet doesn't match the Lists sheet — usually a trailing space from a paste. Use the drop-down.

Common questions

What should I put in Effort?

Days, hours or story points. The unit is irrelevant as long as it's the same everywhere, because it's only ever used as a weight. Rough numbers are fine — the difference between a 2 and a 15 is what matters, not whether it's 12 or 15.

Doesn't this assume work progresses evenly?

It does, and that assumption is wrong for most tasks. It's still useful, because it's wrong in a consistent direction: a task that has used 80% of its time and delivered 20% is worth a conversation whatever shape its real curve is. Treat Expected as a prompt, not a verdict.

Can it handle dependencies?

Not as built, and I'd resist adding them. Dependency chains are where spreadsheets turn brittle — one inserted row and the whole network is wrong. If tasks genuinely can't start until others finish, that's the point to use software built for it.

How do I stop old tasks cluttering the sheet?

Filter Status to hide Done, rather than deleting the rows. Completed work is what makes the effort weighting accurate, and it's the only record you'll have of how long things actually took.

How many tasks will it hold?

120 rows as supplied. Extend by inserting rows inside the existing range so the formulas and validation come with them.

The short version

• Compare progress against elapsed time, not just against the due date.
• MEDIAN(0, x, 1) clamps any value into a range with one function.
• Guard the division before you write it — one bad date shouldn't break a column.
• Test Done first, then Overdue, then Behind. Facts outrank estimates.
• A tolerance stops the tracker crying wolf about normal, lumpy progress.
• Weight completion by effort. Counting tasks flatters you.
• Reference the cell holding a label, never the label itself.

Next steps: the Status column is nested IF logic with the ordering rules that matter, the by-owner block is COUNTIFS with two conditions, and the phase totals are SUMIF. If you're tracking deadlines you're chasing other people for, the job application tracker uses the same idea against dates you don't control.