How to exclude weekends in an Excel Gantt chart

Why does an Excel Gantt chart stop at 52 weeks?

An Excel Gantt chart stops at 52 weeks when its grid has only 52 week columns or its header uses repeating week numbers. A spreadsheet Gantt chart is a grid: one row per task, one column per day or week, and a conditional formatting rule that colours the cells between each task's start and end. If the template that built the grid made 52 week columns, week 53 does not exist, however correct your dates are.

Click the last date in the header row and read the formula bar to identify the cause:

• A fixed run of columns. The header is a start date followed by cells that each add seven days, and it simply stops after 52. This is the easy case: the grid is fine, there just is not enough of it.
• Week numbers instead of dates. The header shows 1, 2, 3 up to 52, often made with WEEKNUM. Week numbers restart every January, so a task in week 3 of next year lands in the same column as week 3 of this year. Adding columns does not help; the header has to change to dates.

Week numbers have a second problem. Microsoft's WEEKNUM documentation describes two numbering systems, and in the default one the week containing 1 January is week 1, however short. In 2027, 1 January is a Friday, so week 1 is three days long and Monday 4 January is already week 2. Under the ISO system that Europe uses, some years have 53 weeks: 2026 is one of them, with a week 53 that starts on Monday 28 December. A header that assumes every year has 52 neat weeks is wrong at one end of the year or the other.

The fix for both is the same principle: put real dates across the top, and make every rule compare dates with dates.

How do you make an Excel Gantt chart run longer than 52 weeks?

Extend the date header and then widen the formatting rule to cover the new columns. If the header uses week numbers, replace it with dates first. If the plan is long enough that daily columns become unreadable, change the scale at the same time.

Extend a header that already uses dates

1. Select the last date cell in the header row and drag its fill handle to the right for as many columns as you need. Each new cell repeats the pattern, adding seven days for a weekly grid or one for a daily one.
2. Go to Home, Conditional Formatting, Manage Rules. Each rule has a range it applies to; widen it to the new last column. If you skip this, the new columns have dates but no bars.
3. Check anything else that stops at the old edge: borders, a print area, or a chart that reads a fixed range.

Replace a week-number header with dates

Put the Monday of the first week in the first header cell, and make every cell after it the previous one plus seven. With task starts in column B and the grid starting in column E, the first cell can find that Monday for you:

Format the header cells to show a short date, such as 4 Jan, and the timeline reads naturally across the year boundary. The weekly rule colours a week if any part of the task falls inside it, which is what you want on a long plan: a three-day task still shows up in its week.

If your Excel uses semicolons as argument separators, every comma in these formulas becomes a semicolon. Excel refuses a conditional formatting rule with the wrong separator without saying why.

Which time scale should you use?

Excel has room for any of them. A worksheet holds 16,384 columns, according to Microsoft's specifications and limits, so even an 18-month plan drawn by the day, about 550 columns, fits easily. The limit is your screen. As a rule of thumb, use daily columns for plans up to about three months, weekly columns up to about two years, and monthly columns beyond that. The weekly version of the example below needs 79 columns, which scrolls but stays readable.

How do you make Excel Gantt dates skip weekends?

Use WORKDAY to calculate end dates from a start date and a working-day duration. Microsoft's WORKDAY function returns the date a given number of working days after a start date, skipping Saturdays, Sundays and any dates you list as holidays. Its partner, NETWORKDAYS, goes the other way and counts the working days between two dates.

Set the sheet up with four columns: Task in A, Start in B, Working days in C and End in D. Then:

Why minus one?

WORKDAY counts forward from the start date, and the start date is day one of the task. A 20-day task that starts on Monday 4 January 2027 ends on Friday 29 January, which is WORKDAY(start, 19). Ask for WORKDAY(start, 20) and you get Monday 1 February, the day after the task finishes, and every task after it slides by a day. NETWORKDAYS counts both ends, so NETWORKDAYS(4 January, 29 January) gives back 20, which makes it a handy check column.

How do you add holidays?

Put the holiday dates in a single column anywhere in the workbook, select them, type Holidays into the Name Box to the left of the formula bar and press Enter. Every formula above can then use the name. The order of the dates does not matter, and adding a date to the list moves every affected task at once.

A start date on a weekend

WORKDAY counts working days forward from the start date, so with any duration above zero it never returns the start date itself, and the end-date formula assumes the task starts on a working day. When a task starts on a Saturday, the count begins on Monday and the task ends one working day early: a five-day task from Saturday 9 January 2027 ends on Thursday 14 January, and NETWORKDAYS counts four working days, not five. Wrapping the first start date in WORKDAY(date-1,1,Holidays) moves a weekend start to the following Monday and leaves a weekday alone.

What if your weekend is not Saturday and Sunday?

WORKDAY assumes a Saturday and Sunday weekend. For anything else, use WORKDAY.INTL, which takes a weekend code before the holiday list. NETWORKDAYS.INTL takes the same codes for durations.

The same 20-day task from 4 January 2027 ends on Friday 29 January with a Saturday and Sunday weekend, on Sunday 31 January with a Friday and Saturday weekend, and on Tuesday 26 January in a six-day week.

How do you grey out weekends on the Gantt grid?

On a daily grid, add a second conditional formatting rule that greys every Saturday and Sunday column, and keep the weekends out of the bars. On a weekly or monthly grid there is nothing to grey out, because weekends sit inside each column, and the working-day dates from WORKDAY already do the job.

1. Select the grid, starting at the first task row under the first date.
2. Add a rule with the formula =WEEKDAY(E$1,2)>5 and a light grey fill. With return type 2, Monday is 1 and Saturday and Sunday are 6 and 7.
3. Change the bar rule so it skips weekends and holidays: =AND(E$1>=$B2,E$1<=$D2,NETWORKDAYS(E$1,E$1,Holidays)=1). NETWORKDAYS of a single day is 1 on a working day and 0 otherwise.
4. If you want holidays grey too, add a third rule, =COUNTIF(Holidays,E$1)>0, with the same fill.

The bar now breaks at each weekend, which is the honest picture of a task that only moves on working days. If you prefer solid bars with grey stripes over them, keep the original bar rule and put the weekend rule above it in Manage Rules, so it wins on the weekend cells.

What does an 18-month Excel Gantt chart look like?

This sample six-phase project starts on Monday 4 January 2027 and runs into July 2028. The holiday list holds ten sample dates, including Good Friday and Easter Monday in both years and the days around Christmas 2027. Only the first start date and the working days are typed; every other date is a formula.

B2 holds the typed start date. D2 is =WORKDAY(B2,C2-1,Holidays), B3 is =WORKDAY(D2,1,Holidays), and both are filled down. A check column with =NETWORKDAYS(B2,D2,Holidays) returns exactly the working days in column C on every row, which is the quickest way to see that the minus one is in the right place.

Design shows the holiday list at work. Its 45 working days would end on Friday 2 April without holidays; with Good Friday and Easter Monday in the list, it ends on Tuesday 6 April, and every later task moves with it.

The whole plan covers 550 calendar days and 385 working days. On a weekly grid starting Monday 4 January 2027, it needs 79 columns, E1 to CE1. Now look at what a 52-column grid would show: its last column is the week of 27 December 2027. Build phase 2 would be cut off in the middle, and Testing and Rollout would not appear at all. Nothing in the sheet would warn you.

On the weekly grid, the header in E1 is =$B$2-WEEKDAY($B$2,2)+1, F1 is =E1+7 filled right to CE1, and the bar rule =AND(E$1<=$D2,E$1+6>=$B2) applies to E2:CE7. The bars run straight across the turn of the year, because the columns are dates, not week numbers.

If you would rather build the bars as a chart object than as coloured cells, the Excel stacked bar method takes these same Start and End columns, and the axis bounds there are set from the first start and the last end.

When should you move the schedule out of Excel?

Consider a dedicated Gantt tool when dates change every week and tasks have several dependencies. The formulas above chain each task to the one before it, which is a dependency of a kind, but only one straight line of them. The moment a task waits on two others, or two streams of work run side by side, you are back to fixing dates by hand, and an 18-month plan has a lot of dates to fix.

Ganttile’s free chart tool, has a Hide weekends setting that makes durations count working days, lets any task depend on any other, and exports the task table back to Excel when someone needs a spreadsheet. The export holds task names, dates and durations, not the bars, so keep the tool as the plan and the spreadsheet as the copy. If you are weighing that move in general, the spreadsheet and browser comparison explains the trade-offs.

Common questions about weekends and long Excel Gantt charts

Why does my Excel Gantt chart stop at 52 weeks?

Because its date header has only 52 week columns, or because it uses week numbers that restart every January. Extend a date header by dragging the last date cell to the right and widening the conditional formatting range, or replace a week-number header with real dates.

Does WORKDAY count the start date?

No. WORKDAY counts forward from the start date, so for a task whose start date is its first working day, the end date is WORKDAY(start, working days - 1). A 20-day task starting Monday 4 January 2027 ends on Friday 29 January.

How do I skip holidays as well as weekends?

List the holiday dates in one column, name the range Holidays in the Name Box, and add it as the last argument: WORKDAY(B2,C2-1,Holidays) for end dates and NETWORKDAYS(B2,D2,Holidays) for durations. The dates can be in any order.

Can Excel exclude only Sundays?

Yes, with WORKDAY.INTL and weekend code 11, which treats Sunday as the only day off: WORKDAY.INTL(B2,C2-1,11,Holidays). Code 7 gives a Friday and Saturday weekend, and a seven-character string such as "0000011" sets any pattern, starting from Monday.

What should you fix first?

Fix the dates before the grid. Add the Holidays list, turn the End column into WORKDAY formulas and check them with NETWORKDAYS. Then change the header to real dates, pick a weekly scale if the plan runs past a few months, and widen the formatting rule to the last column.