# A Project Schedule in Excel: Dependencies, Working Days, and a Gantt Without an Add-In

Two formulas turn a task list into a schedule that skips weekends and holidays and moves when a predecessor slips. The Gantt bars are conditional formatting, not a chart, and the off-by-one in the finish date catches everyone.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-project-schedule-working-days
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## Two Formulas and a Conditional Format

A schedule that recalculates when a task slips needs exactly two formulas: one that starts a task the working day after its predecessor finishes, and one that finds the finish date from a duration in working days.

The Gantt bars are conditional formatting across a row of dates, not a chart. That makes them fast, printable, and free of the alignment problems a stacked bar chart produces.

Tool: [Try the date calculator](https://dothecalculation.com/calculators/date-calculator) — Check any single date calculation against the schedule before trusting the chain.

## The task table

**Columns A to G, one row per task**
| Column | Header | Contents |
| --- | --- | --- |
| A | ID | A sequential number, used by the predecessor column |
| B | Task | Description |
| C | Predecessor | The ID this task waits for, blank for the first |
| D | Duration | Working days |
| E | Start | Formula |
| F | Finish | Formula |
| G | Percent complete | Typed, for the progress bar |

**The two formulas**

```
Start: =IF(C2="", ProjectStart, WORKDAY(INDEX($F$2:$F$60, MATCH(C2, $A$2:$A$60, 0)), 1, Holidays))    ·    Finish: =WORKDAY(E2, D2−1, Holidays)
```
- INDEX and MATCH find the predecessor row by ID, so tasks can be reordered without breaking anything.
- WORKDAY with 1 moves to the next working day after the predecessor finishes.
- The −1 in the finish formula is because the start day counts as day one of the duration.

> **The −1 is the error everyone makes once** — A five-day task starting Monday finishes Friday, not the following Monday. WORKDAY(Monday, 5) returns the next Monday, because it adds five working days to the start. Subtracting one makes the start day count toward the duration, which is what a duration means.

## The holidays range

Put public holidays on their own sheet as a single column of dates and name the range. Every WORKDAY and NETWORKDAYS formula in the workbook then references one list.

Without it, a schedule spanning a public holiday is a day short at every task downstream, and it fails silently. Include company shutdowns and known team absences as well; the function does not care why a day is unavailable.

**A non-standard working week**

```
=WORKDAY.INTL(start, days, "0000011", Holidays)
```
- The seven-character string runs Monday to Sunday, with 1 marking a non-working day.
- "0000011" is the standard weekend; "0000110" makes Friday and Saturday the weekend.
- Use the same string in NETWORKDAYS.INTL wherever you count elapsed working days.

## More than one predecessor

A task waiting on several others starts after the last of them. Put the predecessor IDs in one cell separated by commas and the formula gets messy; a cleaner approach is a small dependency table with one row per link.

**Start after the latest predecessor**

```
=WORKDAY(MAXIFS(FinishColumn, SuccessorColumn, A2), 1, Holidays)
```
- MAXIFS finds the latest finish among every task that feeds this one.
- The dependency table has two columns: predecessor ID and successor ID, one row per link.
- This is more setup than a single predecessor column and it is the only version that handles a real project.

**Lag and lead**

```
Lag of 3 days: =WORKDAY(PredFinish, 1+3, Holidays)    ·    Lead of 2 days: =WORKDAY(PredFinish, 1−2, Holidays)
```
- Lag is a wait, such as concrete curing or a client review window.
- A negative lead starts a task before its predecessor finishes, which is an overlap rather than a dependency.
- Keep lag in its own column rather than adjusting durations, or the schedule stops explaining itself.

## The Gantt bars

Put a row of dates across the top, starting in column I, incrementing by one day or one week. Then apply two conditional formatting rules to the grid below.

**Three rules, applied to the whole grid**
| Rule | Formula | Format |
| --- | --- | --- |
| Task bar | =AND(I$1>=$E2, I$1<=$F2) | Solid fill |
| Completed portion | =AND(I$1>=$E2, I$1<=WORKDAY($E2, $D2*$G2−1, Holidays)) | Darker fill, placed above the bar rule |
| Weekend column | =WEEKDAY(I$1, 2)>5 | Light grey fill, placed last |
| Today marker | =I$1=TODAY() | Left border, placed first |

> **The anchoring is the whole trick** — I$1 locks the row so every cell in a column reads the same date. $E2 and $F2 lock the column so every cell in a row reads the same task dates. Get either wrong and the bars appear diagonally, which is a memorable way to learn how mixed references work.

Rule order matters. Excel applies rules top to bottom and the first match on a given property wins, so the completed-portion rule must sit above the task bar rule or it will never be visible.

## The summary block

**Figures worth putting above the schedule**
| Figure | Formula |
| --- | --- |
| Project start | =MIN(StartColumn) |
| Project finish | =MAX(FinishColumn) |
| Calendar duration | =ProjectFinish − ProjectStart + 1 |
| Working days | =NETWORKDAYS(ProjectStart, ProjectFinish, Holidays) |
| Tasks complete | =COUNTIF(PercentColumn, 1) |
| Overall progress | =SUMPRODUCT(Durations, PercentComplete) / SUM(Durations) |
| Tasks overdue | =SUMPRODUCT(--(FinishColumn<TODAY()), --(PercentColumn<1)) |

The progress row is duration-weighted rather than a simple count, so a half-finished twenty-day task counts for more than a completed one-day task. A count of completed tasks flatters a project that has done all the easy work.

## Float, without a project tool

Total float is how long a task can slip without delaying the project. Computing it properly needs a backward pass through the network, which is where a spreadsheet starts to strain.

A workable approximation: add a column for the latest acceptable finish, entered manually for milestones and derived downward for everything else, then compute float as that minus the calculated finish. Zero float marks the critical path. Below about thirty tasks this is fine; above it, use a scheduling tool.

## What the spreadsheet is not

- A resource model. It says nothing about whether the same person is assigned to three concurrent tasks, which is the most common reason real schedules slip.
- A critical path engine. Proper forward and backward pass calculation across a dependency network is beyond what a formula grid does comfortably.
- A baseline. To track slippage you need the original dates stored separately, since the live formulas overwrite themselves as inputs change.
- A risk model. Every duration here is a single number. Three-point estimates and a simulation give a completion date with a probability attached, which a deterministic schedule cannot.
- Aware of part-time availability. A five-day task done by someone available two days a week takes twelve working days, and the duration column has to carry that.
- A substitute for talking to the team. The schedule records what people said; it does not know whether they believed it.

**Why is my finish date one day late?**

The finish formula needs duration minus one. WORKDAY adds working days to the start date, so a five-day task starting Monday returns the following Monday unless you subtract one. Use =WORKDAY(Start, Duration−1, Holidays).

**How do I make a task start after its predecessor?**

Use =WORKDAY(PredecessorFinish, 1, Holidays), which moves to the next working day. Find the predecessor row with INDEX and MATCH on the ID rather than a direct cell reference, so the table can be reordered without breaking the links.

**How do I exclude public holidays?**

Keep them in a named single-column range of dates and pass it as the last argument to every WORKDAY and NETWORKDAYS formula. Add company shutdowns to the same list; the function does not distinguish between reasons a day is unavailable.

**Why do my Gantt bars appear diagonally?**

The mixed references are wrong. The date row must be locked by row as I$1 and the task dates by column as $E2. If both are fully relative, each cell compares itself to a different task and a different date, which draws a diagonal.

**How do I handle a task with several predecessors?**

Use a separate dependency table with one row per link, then start the task with =WORKDAY(MAXIFS(Finishes, Successor, ThisID), 1, Holidays). It takes more setup than a single predecessor column and it is the only version that survives a real project.

**Can I compute the critical path?**

Approximately. Add a latest-acceptable-finish column and compute float as that minus the calculated finish, with zero float marking the critical path. A proper backward pass across a dependency network is more than a formula grid handles well, and above about thirty tasks a scheduling tool is the better answer.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-project-schedule-working-days). Quote freely with attribution and a link to this page._