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))
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")))))
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")
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)
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)
It returns up to six blocked or overdue tasks, soonest due date first.
7. The weekly check-in
- Set J1 to the meeting date, or leave TODAY() in it.
- Read the needs-attention list before anything else.
- 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)