Most of what gets offered as a critical path method template is a Gantt chart with a column called Start that you fill in yourself. That is not the critical path method. The whole point of CPM is that you never type a date — you type durations and predecessors, and the dates are computed. If the template makes you enter the start, it has quietly handed the work back to you and kept the chart.
So this is what an honest one has to do, which half of it a spreadsheet handles well, which half it fights you on, and the file I ended up with. Numbers throughout come from the same 21-task renovation plan the template ships with.
What the template has to produce
Six numbers per task, and one of them is the answer everybody actually came for:
- Early start / early finish — from the forward pass, the soonest the task could possibly run.
- Late start / late finish — from the backward pass, the latest it could run without moving the project end.
- Total float — late start minus early start.
- Critical — a yes/no that is just total float equals zero.
Get those and the critical path is not something you go hunting for; it is the set of rows where the float column came out zero. On the renovation that is twelve rows out of twenty-one, and their durations sum to 58 working days, which is the project length. I wrote up the method itself in more detail in what a CPM schedule actually is — this post is about getting a spreadsheet to do it.
The forward pass is genuinely a formula
This half a spreadsheet is good at. A task starts the working day after the last of its predecessors finishes, or on the project start date if it has none. Two functions carry almost all of it: WORKDAY, which adds days while skipping weekends, and a lookup that finds the latest finish among the IDs listed in the Predecessors cell.
The Predecessors cell is a comma-separated string — 9,10,11 — so the lookup is the fiddly part rather than the logic. Once it works, Start and Finish stop being input. Change a duration and the whole downstream plan re-dates itself, which is the difference between a schedule and a picture of one. I went through the formulas properly in the Excel Gantt chart write-up.
The backward pass is where it fights back
The backward pass needs each task’s successors. Your sheet stores predecessors. There is no reverse of that lookup sitting there — to find everything that waits on task 7, you have to scan every other row’s Predecessors string for a 7, and take care not to match the 7 inside 17.
It is doable. It is also the point at which the file stops being something you would hand to somebody else, because one array formula per row, per direction, with string parsing inside it, is not a thing anyone maintains. And it gets worse the moment you want lags (start three days after) or link types other than finish-to-start, because each variant changes the subtraction.
This is the honest limitation of every critical path method template in a spreadsheet, including mine. The template calculates the forward pass in formulas. The float and the critical flag come back from the solver into a solution tab, because that is where the backward pass is a dozen lines instead of a maintenance problem.
What is in the file
Five tabs — a read-me, tasks, resources, parameters and an empty solution sheet. Room for 60 tasks across a 95-day grid. The task table is ID, Task, Days, Predecessors, Resource, Crew per day, Start, Finish, Pin and a Check column that tells you when a predecessor sits below its successor or a pinned date lands before something it waits on.
Yellow cells are yours; everything else is a formula. The bars draw themselves, weekends shade out, and typing a date over a Start cell pins that task in place if you need to hold something for a delivery. Nothing is locked or password-protected — pull it apart if you want to see how a cell works. It is free to download, no email.
Upload the same file to the scheduler and it fills the solution tab in: start, finish, total float, critical, and a second float column measured against the crew you actually have. Those two float columns are not the same number, and the gap between them is the subject of its own post.
The limit that matters more than the backward pass
Suppose you do get both passes into a spreadsheet. You still have a plan that assumes anything not forbidden by a dependency can happen at once. The renovation’s critical path method answer finishes on 25 November — and quietly needs five general crew on its busiest day, on a job that has three, over capacity on fourteen separate days.
Schedule it so no day ever exceeds three people and the finish is 2 December. Five days that were nowhere in the CPM output. Ask instead how many people would hold the original date and the answer is five, and one extra body buys you two days rather than five:
That question has no formula behind it — fitting the work inside a fixed headcount is NP-hard, which is why no template computes it and why the levelling button in the commercial packages runs a priority rule and declines to say how close it got.
Which is the split I have settled on after a lot of years of this. The spreadsheet is where the plan lives and where you can see it. The forward pass belongs there. The backward pass and the resource question belong somewhere that can actually solve, and then write the answer back into the same file so the chart you built redraws itself.
Things that I use, like, and am affiliated with:
Mint Mobile offers great cell phone service for $15 flat, get $15 off using the link. Get discounted phones with service activation and no contract.
I never spend money before I check Mr Rebates or Rakuten to get cashbacks, rebates, discounts, coupons or cheaper gift cards.