Excel Gantt Charts: How to Skip Weekends and Holidays

Yes. In Excel for Microsoft 365, you can make a Gantt schedule skip weekends by building its timeline from workdays only, using WORKDAY to calculate task dates, and applying conditional formatting to the timeline cells. A standard stacked-bar Gantt chart is different: its date axis normally includes weekend dates, so a workday-only grid is often the clearest way to show gaps without weekends.

What skipping weekends means in a Gantt chart

A Gantt chart plots tasks against time. In a conventional calendar view, Friday, Saturday, Sunday, and Monday occupy four consecutive date positions, even if nobody is scheduled to work on Saturday or Sunday. Skipping weekends means the display advances from Friday directly to Monday, or that weekend columns remain visible but are not treated as working time. Those are different outcomes, and the right setup depends on whether you want a compact schedule or a calendar-faithful view.

Excel does not have a dedicated, one-click Gantt chart type. A common chart is a stacked bar chart whose first series represents the delay from the project start and whose second series represents task duration. Another practical option is a date grid: task names run down the rows, workdays run across the columns, and conditional formatting colors the scheduled cells. The grid method makes it straightforward to omit weekends from the displayed timeline.

Set up task dates using working days

Start with one row per task. For example, put task names in column A, start dates in B, duration in working days in C, and finish dates in D. Enter actual Excel dates in the date columns, not text that merely looks like a date. If a task starts on a weekday and its duration counts both its first and last working days, calculate its finish date as follows.

With the start date in B2 and the working-day duration in C2, enter =WORKDAY(B2,C2-1) in D2. If B2 is Monday, January 5, 2026, and C2 is 3, the result is Wednesday, January 7, 2026. The minus one makes the start date day one of the task: a one-day task finishes on its start date. Fill the formula down for other tasks.

That example assumes a task starts on a weekday. Decide how your schedule should handle a start date on Saturday or Sunday before building the chart. You might move the planned start to the next working day, or keep the original date as a milestone while scheduling effort from the next working day. Do not assume a weekend start will mean the same thing in every planning convention.

If your plan observes holidays, list them as real dates in a range such as J2:J20. With the same layout, use =WORKDAY(B2,C2-1,$J$2:$J$20) in D2. For a task starting January 5, 2026, lasting three working days, and a holiday on January 6, the finish is January 8. The holiday is excluded from the count. Keep the holiday list current and make sure it contains dates, not weekday names.

Build a timeline that omits weekends

Place the first workday date in G1. To generate the following workday dates across the sheet, put this formula in H1 and copy it to the right: =WORKDAY(G1,1,$J$2:$J$20). With January 9, 2026, in G1 and no intervening holiday, H1 returns January 12, 2026: Friday is followed by Monday. The holiday range is optional; leaving it out makes the formula =WORKDAY(G1,1). Format the header cells as dates so they are easy to read.

For a small schedule, you can also type workday dates into the header row yourself. A formula is more maintainable when the timeline is long or when holidays may change. Copying the formula far enough to cover the project is important; a timeline that ends before the final task will make later task bars appear incomplete.

Suppose task starts are in B2:B30, finishes are in D2:D30, and the workday headers run from G1 across the sheet. Select the schedule area, such as G2:AZ30, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With G2 as the active cell in the selected range, use =AND(G$1>=$B2,G$1<=$D2) and choose a fill color. For a task from January 5 through January 7, the three matching workday columns are highlighted. The dollar signs keep the header row and task date columns anchored correctly as the rule is applied across the grid.

In Excel for Mac, the same core functions and conditional-formatting approach are available; menu placement or wording can differ slightly by release. Excel for the web can open and calculate this kind of workbook, though some formatting controls may be arranged differently from the Windows desktop ribbon. If the schedule is shared with people using different versions, check that the workday headers and formatting appear as intended for them.

Decide whether to hide weekends or show them unfilled

A workday-only header is compact: weekend dates do not take up columns at all. This is useful for a team that plans only Monday through Friday work and wants task lengths to be visually comparable in working days. It can, however, obscure the passage of calendar time. A Friday-to-Monday task appears in adjacent columns even though three calendar days separate those dates.

Alternatively, use every calendar date as a column and leave weekends blank or shade them. This makes elapsed time visible and helps readers understand deadlines, handoffs, or work that can happen over a weekend. To prevent task bars from filling weekend cells, add a weekday test to the conditional-formatting rule.

With dates in G1:AZ1, task start in B2, finish in D2, and G2 as the active cell of the selected schedule range, use =AND(G$1>=$B2,G$1<=$D2,WEEKDAY(G$1,2)<=5). This colors dates that fall within the task span and have weekday numbers from 1 to 5, where Monday is 1 and Sunday is 7. The Saturday and Sunday columns remain visible but unfilled. This formula does not exclude public holidays; include a holiday check or use a workday-only header if holidays must also be omitted.

How this differs from a stacked bar Gantt chart

A typical Excel bar-chart Gantt uses a hidden start-offset series and a visible duration series. To create one, prepare those values, select the data, and choose Insert > Insert Bar Chart > Stacked Bar. Format the offset series with no fill, reverse the task category order if needed, and adjust the horizontal axis. This can be an effective presentation for a calendar-based plan.

However, a date axis represents dates as a continuous numeric scale. If two tasks start on Friday and Monday, their date positions reflect the calendar gap; a normal date axis does not automatically compress weekends out of time. A duration calculated in working days does not, by itself, remove weekend space from that axis. That distinction is the common source of confusion when someone expects a standard bar chart to skip Saturday and Sunday.

If a true workday-only axis is essential, use a grid with workday headers, or build a category-based chart from a prepared list of working dates and test the result carefully. Category spacing can remove the calendar gaps, but it also changes what the horizontal spacing means. For a chart intended to communicate calendar deadlines, hiding weekend gaps may mislead readers. For a planning board focused on work capacity, a workday grid is usually easier to maintain and interpret.

Count working days and check task durations

When start and finish dates are already entered, NETWORKDAYS counts working days inclusively, excluding Saturday and Sunday by default. With a task start in B2 and finish in D2, use =NETWORKDAYS(B2,D2). For January 5 through January 7, 2026, the result is 3. To exclude the dates listed in J2:J20 as well, use =NETWORKDAYS(B2,D2,$J$2:$J$20). The count is useful for checking that a displayed task duration agrees with its start and finish dates.

These functions follow a standard Monday-to-Friday workweek. If your organization works a different weekly pattern, Excel also provides WORKDAY.INTL and NETWORKDAYS.INTL for defining a weekend pattern. Use the same weekend convention throughout the workbook: mixing a custom schedule formula with a Monday-to-Friday counting formula can produce dates and durations that do not agree.

Common problems and how to fix them

The formula displays a number. Excel stores dates as serial numbers. Apply a date format from Home > Number, or use the Number Format control, rather than changing the underlying calculation.
A task appears one day too long or short. Check whether duration counts the start date. The WORKDAY(start,duration-1) pattern treats the start as day one when it is a working day; adding the full duration instead advances one extra working day.
A task still includes a holiday. Confirm the holiday range is included in both the finish-date calculation and, if relevant, the timeline-generation formula. A date written as text may not be recognized as a holiday date.
Bars do not line up with the dates. Check that the header cells contain actual dates, that conditional formatting refers to the correct header row, and that the rule was applied to the intended schedule range. In the conditional-formatting formula, row and column references must match the layout.
The bar chart still has weekend gaps. That is expected on a continuous calendar date axis. Use a workday-only grid or a category-based timeline if weekend positions must disappear; changing the task duration formula alone will not compress the chart axis.
Dates change unexpectedly between regions. Excel recognizes dates according to system and workbook settings. Enter dates using a format your installation recognizes, then verify them by changing the cell format or checking the formula bar.

Choose the display that fits the audience

Use a workday-only grid when the main question is which working days each person or task occupies. Use a calendar grid with unfilled weekends when elapsed time, weekends, or deadlines matter. Use a stacked bar chart when a polished overview is more important than removing every nonworking-day gap. Some projects benefit from both: a compact workday planning view for the team and a calendar-based summary for stakeholders.

Before sharing the workbook, label the timeline clearly as working days or calendar dates. Include the workweek definition and the holiday calendar if they affect commitments. This small amount of context helps readers avoid interpreting three adjacent workday columns as three consecutive calendar days.

Frequently Asked Questions

Can Excel automatically skip Saturdays and Sundays in a Gantt chart?
Not in every kind of Gantt display. WORKDAY can generate workday dates, and a conditional-formatting grid can omit weekend columns. A conventional stacked bar chart on a date axis generally retains calendar spacing, including weekend gaps.

Does WORKDAY count the start date?
WORKDAY moves a specified number of working days before or after a start date; it is not a duration-counting function. For an inclusive task duration beginning on a normal weekday, use the duration minus one to calculate the finish date. Check your project’s convention for weekend starts.

How do I exclude holidays as well as weekends?
Put holiday dates in a cell range and pass that range as the optional holiday argument to WORKDAY or NETWORKDAYS. Use the same list when generating a workday-only timeline so holidays do not appear as working columns.

Can I keep weekend columns visible but leave task bars blank there?
Yes. Use a calendar-date header and a conditional-formatting formula that checks both whether the date is within the task and whether WEEKDAY(date,2) is 5 or less. You can separately shade weekend columns to make the calendar structure clear.

Why does my Gantt chart show a gap between Friday and Monday?
A standard date axis measures calendar time, so Saturday and Sunday take up space even when no work is planned. To remove that space, use workday-only date categories or a workday grid; to preserve a truthful calendar view, leave the gap in place.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.