# Project tracker template: formulas and setup From https://useflori.com/templates/project-tracker-template. Free to copy and change. Two CSV files go with this sheet: - project-tracker-template-tasks.csv: a task tracker for one project (fictional Saltbox Catering, 15 tasks). - project-tracker-template-projects.csv: a multi-project tracker (fictional Northline studio, 7 projects). Names, companies and dates in both files are invented. The CSVs hold values only; add the formulas below after you open them. Every function used (IF, OR, AND, MAX, DAYS, AVERAGE, COUNTA, COUNTIF, COUNTIFS, WEEKDAY) exists in Google Sheets and in Excel 2016 or later. ## 1. Open the CSV - Google Sheets: File, Import, choose the CSV, then Create new spreadsheet. - Excel: open the CSV file directly. Check the dates: type =ISTEXT(F2) in a spare cell. TRUE means the date came in as text. Retype it, or convert it with =DATEVALUE(F2) in a helper column formatted as a date. ## 2. Task tracker (one project) Columns: A Task, B Owner, C Status, D Priority, E Start, F Due, G % complete (0 to 100), H Days late, I Blocker, J Notes. Column L holds labels and column M the as-of date and the summary. - M2: the as-of date. Keep 2026-11-18 to check the sample, or type =TODAY() for daily use. | Cell | Formula | Sample result (as of 2026-11-18) | |---|---|---| | H2, filled down to the last task | =IF(OR(F2="",C2="Done"),0,MAX(0,DAYS($M$2,F2))) | 5 for "Hire two seasonal drivers", 2 for "Confirm turkey and ham quantities", 1 for "Write menu descriptions", 0 for the rest | | M4 Project % complete (format as percent) | =AVERAGE(G2:G200)/100 | 48% (715 / 15) | | M5 Tasks done (format as percent) | =COUNTIF(C2:C200,"Done")/COUNTA(A2:A200) | 33% (5 of 15) | | M6 Overdue tasks | =COUNTIF(H2:H200,">0") | 3 | | M7 Due this week, not done | =COUNTIFS(F2:F200,">="&($M$2-WEEKDAY($M$2,3)),F2:F200,"<="&($M$2-WEEKDAY($M$2,3)+6),C2:C200,"<>Done") | 5 (week of Monday 16 to Sunday 22 November) | | M8 to M11 Count by status | =COUNTIF($C$2:$C$200,L8), filled down | Not started 5, In progress 3, Blocked 2, Done 5 | WEEKDAY(date,3) returns 0 for Monday to 6 for Sunday in both apps, so the as-of date minus it is the Monday of that week. ### Status dropdown (C2:C200) - Google Sheets: Data, Data validation, Add rule, Criteria: Dropdown. Enter Not started, In progress, Blocked, Done. Done. - Excel: Data, Data Validation, Allow: List, Source: Not started,In progress,Blocked,Done. OK. ### Highlight overdue and blocked rows (A2:J200) Rule 1: =$H2>0 (overdue, red fill). Rule 2: =$C2="Blocked" (blocked, amber fill). - Google Sheets: select A2:J200, Format, Conditional formatting, "Format cells if": Custom formula is. Enter the formula, pick a fill, Done. Add another rule for the second formula. - Excel: select A2:J200, Home, Conditional Formatting, Manage Rules, New Rule, "Use a formula to determine which cells to format". Enter the formula, Format, choose a fill, OK, OK. Repeat for the second formula. ## 3. Multi-project tracker Columns: A Project, B Client, C Owner, D Stage (Kickoff, In delivery, Client review, Launch), E Start, F Due, G % complete, H Health, I Next milestone, J Milestone date, K Last update. Column M holds labels and column N the as-of date and the summary. - N2: the as-of date (2026-11-18 in the sample). - H2, filled down: =IF(OR(AND($N$2>F2, G2<100), AND(J2<>"", $N$2>J2)), "Late", IF(OR(G2<100*DAYS($N$2,E2)/DAYS(F2,E2)-15, DAYS($N$2,K2)>7), "At risk", "On track")) The rule, in order: 1. Late: the due date has passed and the project is under 100%, or the next milestone date has passed. 2. At risk: percent complete is more than 15 points behind the share of time used, or the row has not been updated for more than 7 days. 3. On track: everything else. - N4 to N6: =COUNTIF($H$2:$H$50,M4), filled down (labels On track, At risk, Late in M4 to M6). - N7: =AVERAGE(G2:G50)/100, formatted as percent. Expected sample results: Website refresh On track; November SEO retainer At risk (last update 9 days old); Brand guidelines Late (due 13 November passed); Newsletter setup On track; Winter sale campaign At risk (59% of time used, 30% done); Trade show graphics On track; Annual report design On track. Totals: 4 On track, 2 At risk, 1 Late; average 47% (330 / 7). ## 4. Weekly routine | When | Who | What | Time | |---|---|---|---| | Friday by 4pm | Each task owner | Update status, % complete and blocker on their own rows only | 5 minutes | | Monday 9:00 | Project lead | Sort by days late; send one message per blocker naming who owes what | 15 minutes | | Monday 9:15 | Owner of the business | Call the owner of every At risk or Late project; write the date in Last update | 10 minutes per 5 projects | When a task is set to Done, write "Done" and the date in Notes, so the weekly status report can list what finished in the period.