DEV Community

Cover image for How to Build a Project Tracker in Google Sheets With Overdue and Blocked Flags
DailyTech Hub
DailyTech Hub

Posted on AI-assisted

How to Build a Project Tracker in Google Sheets With Overdue and Blocked Flags

A task list only shows what exists. Two calculated columns, Days late and Flag, plus a rule for which one wins when a task is both blocked and late, turn it into a tracker that shows what needs attention today.

This tutorial builds that in Google Sheets in about 20 minutes. The layout is simplified, and the example is a made-up website relaunch with 24 tasks.

1. Columns and the status date

Put these headers in row 1:

A B C D E F G
Task Owner Status Due % done Days late Flag

You type into A to E. F and G calculate. Rows 2 to 61 give you 60 tasks.

Two settings go beside the table: J1 is the Status date, the day the tracker measures against (label it in I1), and J2 is the Due soon window in days (label in I2), set to 7. Type a date in J1, or type =TODAY() to keep it current. A typed date lets you reproduce last Friday's picture, and TODAY() keeps the sheet live.

2. Lock the status words

Select C2:C61, then Data > Data validation > Add rule. Set Criteria to Dropdown and add four items: Not started, In progress, Blocked, Done.

Every formula below counts those exact words, so the dropdown matters. A typo like "Blockd" would silently fall out of every count.

3. Days late

In F2:

=IF(A2="", "", IF(AND(C2<>"Done", D2<>"", D2<$J$1), $J$1-D2, 0))
Enter fullscreen mode Exit fullscreen mode

A task counts as late only if it is not Done, has a due date and that date is before the Status date. A task due on the Status date is not late yet. If Sheets shows F as dates, set Format > Number > Number.

4. The Flag, in a fixed order

In G2:

=IF(A2="", "", IF(C2="Done", "Done", IF(C2="Blocked", "Blocked", IF(F2>0, "Overdue", IF(AND(D2<>"", D2-$J$1<=$J$2), "Due soon", "On track")))))
Enter fullscreen mode Exit fullscreen mode

The order of the tests decides the result. Done comes first, so finished work never gets flagged. Blocked comes before Overdue, so a task that is both reads Blocked, and column F still shows how many days late it is. Copy F2:G2 down to row 61.

With a Status date of 10/19/2026, five tasks from the sample project show the edge cases:

Task Status Due Days late Flag
Icon and image set Blocked 10/16 3 Blocked
Write homepage copy In progress 10/16 3 Overdue
Privacy page text from legal Blocked 10/19 0 Blocked
Design review Not started 10/26 0 Due soon
Build page templates In progress 10/28 0 On track

The Design review is due exactly 7 days after the Status date, so it still counts as Due soon. The page templates are due 9 days out.

5. Make the problem rows red

Select A2:G61, open Format > Conditional formatting, and under "Format cells if" choose Custom formula is:

=OR($G2="Blocked", $G2="Overdue")
Enter fullscreen mode Exit fullscreen mode

Pick a light red fill. The $ before G makes the whole row change, not only the flag cell.

6. A summary you can read in ten seconds

Put labels in I4:I9 and these formulas in column J:

J4: =COUNTA(A2:A61)
J5: =COUNTIF(C2:C61, "Done")
J6: =COUNTIF(C2:C61, "In progress")
J7: =COUNTIF(C2:C61, "Blocked")
J8: =COUNTIF(G2:G61, "Blocked") + COUNTIF(G2:G61, "Overdue")
J9: =IF(J4=0, 0, SUM(E2:E61)/J4)
Enter fullscreen mode Exit fullscreen mode

For the sample project that gives 24 tasks, 8 done, 5 in progress, 2 blocked and 3 needing attention. J9 is 11.25 / 24 = 0.46875, so 47%. Marking a task Done does not change its % done, so type 100% when you finish something, or J9 undercounts.

For a live list, put this in L1:

=QUERY(A1:G61, "select A, B, D, G where G = 'Blocked' or G = 'Overdue' order by D limit 6", 1)
Enter fullscreen mode Exit fullscreen mode

It returns up to six blocked or overdue tasks, soonest due date first.

7. The weekly check-in

  1. Set J1 to the meeting date, or leave TODAY() in it.
  2. Read the needs-attention list before anything else.
  3. Update Status and % done, then read J8 again.

If you want it ready-made

There is a free project tracker template with this logic on a Tasks tab and a Dashboard with charts, as a Google Sheets copy or an .xlsx. The formulas above are enough to build your own.

If you track a project in a sheet today, what is the one status your list is missing: waiting on someone else, at risk, or something else?

Top comments (0)