Pro workbook: Tasks with Gantt, Dashboard, Milestones, Setup, Formulas. Sample data, real formulas.
| Project tracker | ||||||||||||||||||||||||||||||||||
| Yellow cells are inputs. Duration, days left and the Gantt bars are calculated — the bars are conditional formatting, not shapes. | ||||||||||||||||||||||||||||||||||
| Timeline starts | 2026-08-31 | Today | 2026-09-11 | |||||||||||||||||||||||||||||||
| Phase | Task | Owner | Start | End | Status | Done | Days | Left | 31 Aug | 7 Sep | 14 Sep | 21 Sep | 28 Sep | 5 Oct | 12 Oct | 19 Oct | 26 Oct | 2 Nov | 9 Nov | 16 Nov | 23 Nov | 30 Nov | 7 Dec | 14 Dec | 21 Dec | 28 Dec | 4 Jan | 11 Jan | 18 Jan | 25 Jan | 1 Feb | 8 Feb | 15 Feb | 22 Feb |
| Discovery | Stakeholder interviews | Priya | 2026-08-31 | 2026-09-11 | Done | 100.0% | 12 | |||||||||||||||||||||||||||
| Discovery | Support inbox review | Priya | 2026-09-07 | 2026-09-16 | In progress | 60.0% | 10 | 5 | ||||||||||||||||||||||||||
| Discovery | Research report | Priya | 2026-09-17 | 2026-09-23 | Not started | 0.0% | 7 | 12 | ||||||||||||||||||||||||||
| Design | Flows and wireframes | Mara | 2026-09-14 | 2026-10-02 | In progress | 30.0% | 19 | 21 | ||||||||||||||||||||||||||
| Design | Visual design | Mara | 2026-10-05 | 2026-10-23 | Not started | 0.0% | 19 | 42 | ||||||||||||||||||||||||||
| Design | Usability test round 1 | Priya | 2026-10-12 | 2026-10-16 | Not started | 0.0% | 5 | 35 | ||||||||||||||||||||||||||
| Build | API and data model | Teo | 2026-09-21 | 2026-10-16 | In progress | 20.0% | 26 | 35 | ||||||||||||||||||||||||||
| Build | Onboarding screens | Jonas | 2026-10-12 | 2026-11-06 | Not started | 0.0% | 26 | 56 | ||||||||||||||||||||||||||
| Build | Payments integration | Teo | 2026-10-19 | 2026-11-13 | Blocked | 0.0% | 26 | 63 | ||||||||||||||||||||||||||
| Build | Admin dashboard | Jonas | 2026-11-02 | 2026-11-20 | Not started | 0.0% | 19 | 70 | ||||||||||||||||||||||||||
| Launch | QA and bug bash | Jonas | 2026-11-16 | 2026-11-27 | Not started | 0.0% | 12 | 77 | ||||||||||||||||||||||||||
| Launch | Beta with 20 customers | Mara | 2026-11-23 | 2026-12-11 | Not started | 0.0% | 19 | 91 | ||||||||||||||||||||||||||
| Launch | Public launch | Teo | 2026-12-14 | 2026-12-18 | Not started | 0.0% | 5 | 98 | ||||||||||||||||||||||||||
| Dashboard | |||
| Where the project stands today, from the Tasks sheet. | |||
| Status | Tasks | Share | |
| Not started | 8 | 61.5% | |
| In progress | 3 | 23.1% | |
| Blocked | 1 | 7.7% | |
| Done | 1 | 7.7% | |
| All tasks | 13 | ||
| Health | Value | ||
| Overdue (past end, not done) | 0 | ||
| Due in the next 7 days | 1 | ||
| Blocked | 1 | ||
| Overall progress (weighted by days) | 14.1% | ||
| Project end (latest task) | 2026-12-18 | ||
| Days to end | 98 | ||
| Owner | Open tasks | Days of work left | Done |
| Mara | 3 | 51 | 0 |
| Teo | 3 | 52 | 0 |
| Priya | 3 | 16 | 1 |
| Jonas | 3 | 57 | 0 |
| Milestones | |||||
| Dates that matter, with how far away they are and whether the tasks before them are done. | |||||
| Milestone | Date | Days away | Phase | Phase tasks | Phase done |
| Research signed off | 2026-09-25 | 14 | Discovery | 3 | 1 |
| Design approved | 2026-10-26 | 45 | Design | 3 | 0 |
| Feature complete | 2026-11-20 | 70 | Build | 4 | 0 |
| Public launch | 2026-12-18 | 98 | Launch | 3 | 0 |
| Setup | ||
| Timeline start (a Monday works best), today's date, and the lists behind the dropdowns. | ||
| Timeline starts | 2026-08-31 | |
| Today | 2026-09-11 | Type =TODAY() to keep it live; a fixed date keeps the file stable for sharing. |
| Statuses | Owners | |
| Not started | Mara | |
| In progress | Teo | |
| Blocked | Priya | |
| Done | Jonas | |
| Keep the four statuses as they are — the Dashboard and the bar colours read them by name. Owners are yours. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. The Gantt is conditional formatting — no shapes, no macros. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Tasks · Days | IF(E6="",0,E6-D6+1) | Calendar days from start to end, inclusive. Empty rows give 0, hidden by the number format 0;-0;;@ — a blank text would break the Dashboard's arithmetic. |
| Tasks · Left | IF(OR(E6="",F6="Done"),"",E6-$D$3) | Days between today (Setup) and the end date. Negative — shown red — means overdue. Done tasks show nothing. |
| Tasks · week header | $B$3+7 | Each Gantt column is one week: the timeline start plus 7, 14, 21… days. |
| Tasks · Gantt planned bar | Conditional format: =AND($D6<>"", J$5<=$E6, J$5+6>=$D6) | A week is shaded when it overlaps the task: the week starts on or before the task ends, and the week's last day is on or after the task starts. |
| Tasks · Gantt done bar | Conditional format: =AND(…, J$5 < $D6+($E6-$D6+1)*$G6) | Same overlap test, plus: the week starts before the 'progress front' — start date plus duration × Done%. Solid blue, and it stops the lighter rule from also firing. |
| Tasks · Gantt blocked | Conditional format: =AND($F6="Blocked", …overlap…) | Blocked tasks draw their bar in red instead. |
| Tasks · today line | Conditional format: =AND(J$5<=$D$3, J$5+6>=$D$3) | The column whose week contains today gets a red left border. |
| Dashboard · status counts | COUNTIF(Tasks!$F$6:$F$60, A5) | How many tasks carry each status; the share divides by the total task count. |
| Dashboard · Overdue | COUNTIFS(Tasks!E:E, "<"&Setup!B4, Tasks!F:F, "<>Done", Tasks!E:E, "<>") | End date before today, not done, and not blank. |
| Dashboard · Overall progress | SUMPRODUCT(Done%, Days)/SUM(Days) | Each task's Done% weighted by its length in days, so a three-week task counts three times a one-week one. |
| Dashboard · Days of work left | SUMPRODUCT((Owner=A20)*(Status<>"Done")*(1-Done%)*Days) | For one owner: the unfinished share of each of their open tasks, in days, added up. |
| Milestones · Phase done | COUNTIFS(Tasks!A:A, D5, Tasks!F:F, "Done") | How many tasks in the milestone's phase are done, against the phase's task count beside it. |