How to Make a Gantt Chart in Excel With Working Days

Hartcliffe Interiors planned a shop refit as ten tasks and forty working days. The programme sheet promised handover on Friday 10 April. The same ten tasks, booked on days the site is open, finish on Tuesday 28 April.

Nothing in the file is a guess. Every duration came from the trade that quoted it, and every finish date is the start plus the duration, which is the arithmetic anybody would write.

=B2+C2-1 counts Saturdays, Sundays and bank holidays as working time. Between 2 March and 10 April there are forty calendar days, and twelve of them are days when nobody is on site.

Programme span, 2 Mar to 10 Apr      40 calendar days
Working days inside it               28
Days of work in the ten tasks        40

Forty days of work will not fit into twenty-eight days of site time. The eighteen calendar days between the two handover dates are not a delay: they were never in the plan.

A Gantt chart drawn from those dates is not wrong about the bars. It is wrong about the calendar underneath them, and it draws the error at full width, in colour, where it looks like a plan.

01Ten Tasks, Forty Days, Twenty-Eight on Site

Columns D and E are the same ten durations read two ways. D is what the sheet calculated by adding days to a date; E is where those days land when the weekend is not a working day.

The two agree on the first task and on nothing after it. By the tenth row they are twelve working days apart, and twelve is exactly the number of Saturdays, Sundays and bank holidays inside the original span.

Three tasks are booked to start on a day the site is locked — Saturday 21 March, Saturday 28 March and Easter Sunday. Two more are booked to finish on one.

02WORKDAY: The Finish That Skips the Weekend

WORKDAY takes a start date and a number of working days and returns the date you land on, stepping over Saturdays, Sundays and every date in a holiday list:

=WORKDAY(B2,C2-1,Holidays)

On the plasterboard row that is six working days from Wednesday 11 March, and it returns Friday 20 March where the sheet had Monday 16 March. Same six days of work, four days further down the calendar.

The function does not care how many weekends it crosses. Ask for six working days and you get six working days, whether they sit inside one week or straddle Easter.

WORKDAY and NETWORKDAYS have shipped in every version since Excel 2007 and both exist in Google Sheets. The .INTL variants arrived in 2010.

A site that works Saturdays wants =WORKDAY.INTL(B2,C2-1,11,Holidays), where 11 means Sunday is the only weekend day. The weekend argument also takes a seven-character string of 1s and 0s for anything stranger.

03The Minus One in Every Duration

A four-day task that starts on Monday finishes on Thursday, not Friday. The first day is one of the four, so the finish is the start plus three.

That is what the C2-1 is doing, and it is the single most common error in a hand-built programme. =WORKDAY(B2,C2) quietly adds a day to every task on the sheet.

Ten tasks, ten extra days, and the error compounds because each task starts from the task before it.

A one-day task is the test. If your formula says a one-day task finishes tomorrow, the minus one is missing, and every date below it is a day late.

04Each Task Starts on the Next Working Day

The start of each task is the finish of the one before it, moved on by one working day:

B3:  =WORKDAY(D2,1,Holidays)

=D2+1 is what the programme had, and it is why the joiners were booked in for Saturday 21 March. WORKDAY with a 1 moves a Friday finish to Monday and leaves a Tuesday finish on Wednesday.

Two formulas now run the whole plan: this one down the start column, and the WORKDAY finish down column D. Change any duration and every date below it moves.

Nothing in a working programme should be a typed date except the first start and the holiday list. A typed date in the middle is a dependency that stops working the moment anything above it changes.

05The Holiday List Is Part of the Formula

The third argument is a range of dates, and leaving it out is the second half of this fault. Good Friday and Easter Monday are two of the twelve days this programme worked through.

Put the dates in a column of their own, name the range Holidays, and both functions read the same list:

J2:  3 Apr 2026    Good Friday
J3:  6 Apr 2026    Easter Monday

A name beats $J$2:$J$3 because the list grows. Next year's dates go on the end, the name follows, and no formula needs editing.

Type the dates with =DATE(2026,4,3) if your workbook crosses regions. A date built that way cannot be read as 4 March by a machine set to a different order, which is how text dates get into the list in the first place.

A holiday that is text rather than a date returns #VALUE! — for the whole column, not just that row. It is the one error this setup throws, and it means somebody typed a date Excel did not recognise.

Empty cells inside the holiday range are ignored, so a named range with room to grow is safe. Duplicates are ignored too, which matters when two regions share a list.

Holidays that fall on a weekend are already skipped and count once. A Christmas Day on a Saturday with a substitute day on the Monday needs the Monday in the list, not the Saturday.

06NETWORKDAYS Reads the Duration Back Out

NETWORKDAYS is WORKDAY run backwards: give it two dates and it counts the working days between them, including both ends:

=NETWORKDAYS(B2,D2,Holidays)

On every row of a correct programme that returns the number already sitting in the Days column. It is the cheapest check on the sheet, and it catches the one thing formulas cannot see.

A row where the check disagrees with the duration is a date somebody typed over. On this programme it disagrees on nine rows out of ten.

The same function answers the question the client asks. =NETWORKDAYS(B2,E11,Holidays) is the whole job in working days, and =E11-B2+1 is the same job in days off the wall calendar: 40 and 58.

07The Timeline Is Conditional Formatting

A Gantt chart does not need a chart. Put one column per day across the top, point a conditional formatting rule at the block underneath, and the bars draw themselves:

L1:  =B2                       the first day on site
M1:  =WORKDAY(L1,1,Holidays)   and fill right

Select the grid from L2 down to the last task, open Home ▸ Conditional Formatting ▸ New Rule ▸ Use a formula, and give it one test:

=AND(L$1>=$B2,L$1<=$D2)

The dollar signs are the entire trick. L$1 locks the row so every cell reads the date header above its own column; $B2 locks the column so every cell reads the dates on its own row.

One rule paints every bar. Change a duration and the bars move with the dates, because the rule is reading the same two cells the formulas wrote.

08Shading the Days Nobody Is on Site

A timeline built on working days still has to show the days it skipped, or the bars look like they have gaps for no reason. Two more rules do it:

=WEEKDAY(L$1,2)>5              the weekend
=COUNTIF(Holidays,L$1)>0       the bank holidays

WEEKDAY with a second argument of 2 numbers Monday as 1, so anything above 5 is a Saturday or a Sunday. COUNTIF against the named list catches Easter.

Rule order decides which colour wins. Excel applies the rules top down, and where two rules set the same fill the higher one takes it. Put the weekend and holiday rules above the bar rule and tick Stop If True on both.

A bar that runs over a shaded weekend is telling the truth: the task is still open, and nobody is working on it. A bar that paints over the shading is the original mistake drawn in green.

09Painting Progress Inside the Bar

Add a per-cent complete column in H and a second bar rule fills in how much of each task is done. The done part of a bar ends a number of working days into it:

=AND(L$1>=$B2,L$1<=WORKDAY($B2,ROUND($C2*$H2,0)-1,Holidays))

$C2*$H2 is the days of work finished, ROUND turns 2.8 days into 3 because a column is a whole day, and the minus one is the same first-day rule as section 3.

This rule goes above the plain bar rule, without Stop If True, so the finished part is painted dark and the rest of the bar keeps the lighter fill underneath.

A progress bar is a claim, not a calculation. Nothing in the sheet knows how much is done, so the per-cent column has to come from somebody who was on site.

10The Bar Chart Version and Its Four Settings

A real chart is better when the plan leaves the sheet — a slide, a printed programme, a client pack. It is a stacked bar chart with two series, and it needs four settings before it looks like anything:

1. Series 1 is the start dates, set to No fill. It is there to push the bar across the chart, not to be seen.
2. Series 2 is the durations, in working days, from =NETWORKDAYS(B2,D2,Holidays).
3. Categories in reverse order, on the vertical axis, or task 1 sits at the bottom and the plan reads upwards.
4. The horizontal axis minimum typed as a date. Left on Automatic it starts at zero, and every bar is drawn from 1 January 1900.

The chart cannot skip weekends. It draws a continuous date axis, so a task that spans a weekend is drawn two days longer than it is worked — which is the one thing the conditional formatting version gets right.

11Does It Still Hit the Deadline?

The only question anybody asked is whether the shop opens on the day the lease says it does. One cell answers it:

=NETWORKDAYS(Deadline,MAX(E2:E11),Holidays)-1

MAX over the finish column is the day the job ends, because the last row is not always the latest date once tasks run in parallel. NETWORKDAYS counts both ends, so the minus one turns "same day" into zero.

On this programme the deadline is Friday 10 April and the answer is 12: twelve working days late, found in a cell rather than on site.

One more cell says who is on site this morning: =COUNTIFS($B$2:$B$11,"<="&TODAY(),$D$2:$D$11,">="&TODAY()) counts the tasks whose start has passed and whose finish has not.

Negative is slack and positive is late. A plan that answers 0 is a plan with no room in it at all, which is worth knowing before the first trade arrives.

12Seven Things That Bite

1. =B2+C2-1 instead of WORKDAY. It is right for calendar days and wrong for everything a trade quotes, and it fails quietly because the dates it returns are real dates.
2. The missing minus one. =WORKDAY(B2,C2) makes every task a day longer; a one-day task finishing tomorrow is the tell.
3. No holiday list. Two bank holidays at Easter and three in May move a summer programme by a working week.
4. A text date in the holiday range. The whole column returns #VALUE!, and the fix is in the list, not the formula.
5. Typed dates in the middle of the chain. They look identical to calculated ones and they do not move when a duration changes.
6. Weekend shading below the bar rule. The bar wins the fill and the timeline stops showing the days the site is shut.
7. A chart axis left on Automatic. Bars drawn from January 1900 are not a formatting problem, they are the axis minimum sitting at zero.

13Mini Exercises

1. On the sample sheet, write =NETWORKDAYS(B2,D2,$J$2:$J$3) beside the first task and fill it down. Expect 4 on row 2 and a number smaller than the Days column on nine rows.
2. Rebuild column D as =WORKDAY(B2,C2-1,$J$2:$J$3) and column B from row 3 as =WORKDAY(D2,1,$J$2:$J$3). Expect the last finish to move from Friday 10 April to Tuesday 28 April.
3. Count the days off: =E11-B2+1 for the calendar span and =NETWORKDAYS(B2,E11,$J$2:$J$3) for the working one. Expect 58 and 40.
4. Build the timeline header across fifteen columns and apply =AND(L$1>=$B2,L$1<=$D2). Confirm the first bar covers four columns.
5. Delete Good Friday from the holiday list and recalculate. Expect the handover to move back one working day, to Monday 27 April.

What to Take Away

A Gantt chart is a picture of two columns of dates, and the picture is only ever as honest as they are. Every argument about a programme is really an argument about whether + or WORKDAY was used four columns to the left.

So the fault here is not the chart, and it is not the durations either. Each trade quoted the work correctly, in the only unit a trade uses, and the sheet then spent those days on Saturdays.

One WORKDAY in the finish column and one named holiday list would have shown the real date in February, when it was still a conversation about scope rather than a letter about handover. Twelve working days, ten tasks, and not a single wrong number anywhere on the sheet.