How to Remove Weekends From an Excel Gantt Chart in 5 Steps

Weekends can make an Excel Gantt chart look longer than the work really is. A five-day task may stretch across seven calendar columns, making deadlines harder to read and progress easier to misjudge.

The problem gets worse when several tasks cross Saturdays, Sundays, and holidays. Your bars show empty time, your milestones drift visually, and your team may mistake calendar duration for working duration.

But here's the truth: Excel can skip weekends if you build the timeline with working-day formulas and configure the chart around that timeline.

This guide shows you how to remove weekends from an Excel Gantt chart in five practical steps, including formulas, chart settings, holiday handling, and common fixes.

How to Remove Weekends From an Excel Gantt Chart in 5 Steps

The most reliable approach is to create a weekday-only timeline, calculate task positions in working days, and use those values in your stacked bar chart.

1. Set up your task schedule

   Start with a simple task list containing the task name, start date, finish date, and working duration.

   Task	Start date	Finish date	Duration
   Design homepage	June 3	June 7	5 working days
   Review layout	June 10	June 11	2 working days
   Build page	June 12	June 18	5 working days

   Assume the project start date is in B2. Place task names in column A, start dates in column B, and finish dates in column C.

   Keep the original calendar dates visible. You will use additional helper columns for the weekday-only calculations.

2. Create a weekday-only timeline

   In a separate row, create the dates that should appear across the Gantt chart. The WORKDAY.INTL function skips weekends automatically.

   Enter the first working date in F2:

   =WORKDAY.INTL($B$2-1,1,"0000011")

   Then enter this formula in G2 and copy it to the right:

   =WORKDAY.INTL(F2,1,"0000011")

   The weekend pattern "0000011" tells Excel that Saturday and Sunday are nonworking days. Each 0 represents a working day, while each 1 represents a weekend day.

   Your timeline will now move from Friday directly to Monday. For example, June 7 will be followed by June 10, with June 8 and June 9 excluded.

3. Calculate working-day offsets and durations

   A standard Gantt chart usually needs two values: an invisible offset before each task starts and the visible task duration.

   Use a helper column for the offset. If the project start date is in $B$2 and the task start date is in B5, enter:

   =NETWORKDAYS.INTL($B$2,B5-1,"0000011")

   This counts the working days before the task begins. A task starting on the project’s first working day receives an offset of zero.

   For the task duration, use:

   =NETWORKDAYS.INTL(B5,C5,"0000011")

   This counts only Monday through Friday between the start and finish dates. A task running from Friday through Monday receives two working days, rather than four calendar days.

   For a task starting June 7 and finishing June 10, the duration formula returns 2. That is the key calculation that prevents the weekend from expanding the task bar.

4. Build or update the stacked bar chart

   Select the task names, offset values, and duration values. Then choose Insert, Bar Chart, and Stacked Bar.

   Excel will create two series. The first series represents the offset, while the second represents the task duration.

   Format the offset series with No Fill and No Line. The duration series becomes the visible Gantt bar.

   Right-click the vertical task axis and choose Format Axis. Select Categories in reverse order if the first task appears at the bottom.

   For the horizontal axis, use the weekday-only timeline as category labels when your chart layout supports category labels. This makes Friday appear immediately before Monday.

5. Format and test the calendar

   Apply a readable date format such as mmm d to the weekday timeline. Use a single color for ordinary tasks and a contrasting color for milestones or deadlines.

   Test at least three cases:

   • A task ending on Friday.
   • A task beginning on Monday.
   • A task running across a weekend.

   Check that the weekend does not create a blank gap or add extra duration. If a task from Friday to Monday appears as four days, one of the chart series is still using calendar dates instead of working-day values.

   Here's why: changing the axis display alone may hide weekend labels, but it does not always remove weekend time from the chart scale. The formulas must also use working-day calculations.

Why Excel Gantt Charts Show Weekends

Excel stores dates as sequential numbers. A calendar axis therefore treats Saturday and Sunday as real intervals, even when no one plans work on those days.

For example, a task beginning on Friday and ending on Monday spans four calendar dates. A normal date calculation sees Friday, Saturday, Sunday, and Monday.

A working-day calculation sees only Friday and Monday. That difference affects both the bar length and the distance between task positions.

Let me explain: the chart cannot decide whether Saturday is a working day. You must define the calendar through formulas, axis settings, or both.

Two Ways to Hide Nonworking Days

Use a weekday-only helper timeline

This is the most dependable method when you need accurate task placement. The helper timeline contains Monday-to-Friday dates only, and the chart uses working-day offsets.

It works well for project plans with many cross-weekend tasks. It also gives you a clear place to add holidays and custom work schedules.

Change the axis to a text axis

If your Gantt chart uses date labels as categories, right-click the horizontal axis and choose Format Axis. Under Axis Options, select Text axis.

This can remove empty weekend positions when each visible label represents a category. However, it may not fix task durations calculated with ordinary date subtraction.

Use this option for a quick visual correction. Use working-day formulas when the schedule must remain mathematically accurate.

Comparison of the two approaches

How to Include Holidays

Weekends are only one part of a working calendar. Public holidays, company shutdowns, and planned breaks can also distort your Gantt chart.

Create a holiday list in a separate range, such as $J$2:$J$20. Then add that range to your formulas.

For the next working date, use:

=WORKDAY.INTL(F2,1,"0000011",$J$2:$J$20)

For the task duration, use:

=NETWORKDAYS.INTL(B5,C5,"0000011",$J$2:$J$20)

For the task offset, use:

=NETWORKDAYS.INTL($B$2,B5-1,"0000011",$J$2:$J$20)

You might be wondering: what happens when a holiday falls on Saturday or Sunday? Excel ignores it because the date already belongs to a nonworking day.

For a project starting Friday and ending Tuesday, with Monday marked as a holiday, the working duration is two days: Friday and Tuesday.

Common Formula and Chart Mistakes

Using calendar subtraction

The formula =C5-B5+1 counts every calendar date. It includes weekends and holidays, so it should not calculate the duration of a weekday-only task.

Replace it with =NETWORKDAYS.INTL(B5,C5,"0000011") when Saturday and Sunday are nonworking days.

Using the wrong weekend pattern

The code "0000011" represents Saturday and Sunday as weekends. A Sunday-Monday schedule needs a different pattern.

Excel also supports numeric weekend codes. Check the pattern carefully before copying formulas across the schedule.

Formatting dates as text too early

Labels such as “Mon” and “Tue” are useful for presentation, but formulas need genuine Excel dates. Keep the timeline as dates, then apply a display format.

If Excel treats the labels as text from the beginning, WORKDAY.INTL and NETWORKDAYS.INTL may return errors.

Leaving the offset series visible

The offset series must be transparent. If it remains colored, the chart will show a second bar before every task.

Click the offset series, open formatting options, and select No Fill. Keep only the duration series visible.

Expecting axis labels to change task length

Removing weekend labels does not automatically shorten a task bar. The chart still uses its underlying values.

Always inspect both the axis type and the formulas behind the offset and duration series.

Natural Project Scheduling Solution: ONES.com

For small schedules, Excel can work well. As tasks, dependencies, approvals, and updates multiply, a dedicated project workspace can reduce manual calendar maintenance.

ONES.com combines project management and knowledge management on one platform. ONES Project is the project management product and a Jira alternative, while ONES Wiki is the knowledge management product and a Confluence alternative. They are sold separately.

Value Proposition

ONES.com helps project teams manage working schedules, workflow rules, reporting, and project knowledge without rebuilding every calendar calculation manually.

You can use cloud, on-premise, private cloud, or air-gapped deployments, with full feature parity between the cloud and self-hosted versions.

Core Capabilities

• Weekend planning pain: Manual date formulas become difficult to maintain. ONES capability: Configure project schedules and working calendars in a centralized workspace. Result: Teams can plan around nonworking days with fewer repeated calculations.
• Changing task sequences: Moving one task can require several manual edits. ONES capability: Use Jira-compatible workflows and sprint management. Result: Schedule changes follow a structured project process.
• Approval delays: Review steps can remain hidden in a simple chart. ONES capability: Create custom workflows and fields. Result: Approval states, owners, and handoffs become easier to track.
• Limited visibility: A static chart may show dates without explaining progress. ONES capability: Use built-in reporting. Result: Project health and delivery trends are available without rebuilding charts.
• Plugin dependence: Separate add-ons can create maintenance work. ONES capability: Combine native project features with automation. Result: Common scheduling and workflow needs can require fewer plugins.
• Restricted-network requirements: Some teams cannot place project information in a public cloud. ONES capability: Support on-premise, private cloud, and air-gapped deployments. Result: Teams can use a controlled environment for sensitive planning.
• Scattered project knowledge: Decisions and delivery plans can live in separate places. ONES capability: Pair ONES Project with ONES Wiki when both products are needed. Result: Project execution and team knowledge can stay connected.

Application Scenarios

Software release planning: A development team can manage sprints, dependencies, approvals, and reporting in ONES Project. The team spends less time adjusting a visual schedule after each scope change.

Engineering work in a restricted network: An organization can deploy the platform on-premise or in an air-gapped environment. Engineers can maintain workflows and project visibility without relying on a public cloud connection.

Cross-functional campaign delivery: Marketing, design, and product teams can use custom fields and workflow steps for reviews, handoffs, and launch readiness. ONES Wiki can hold supporting guidance when the knowledge management product is included.

Common Challenges and Practical Fixes

Weekend columns still appear

Problem: The horizontal axis is using calendar dates.

Solution: Replace the timeline with weekday-only categories, or switch to a text axis when the chart uses category labels.

Task durations are too long

Problem: The duration formula uses date subtraction.

Solution: Use NETWORKDAYS.INTL for task length and include the holiday range when needed.

Monday starts in the wrong position

Problem: The offset calculation includes the task’s start date.

Solution: Calculate the offset through the day before the task begins by using B5-1 in the second argument.

Holiday dates are ignored

Problem: The holiday range is missing from one or more formulas.

Solution: Add the same holiday range to the timeline, offset, and duration calculations.

FAQs

Can I remove weekends without rebuilding my entire Gantt chart?

Sometimes. If your chart uses category labels, changing the horizontal axis to a text axis may remove visible weekend gaps. However, this only changes the display in many setups. For accurate durations and task positions, add weekday-only helper values and update the offset and duration formulas.

What formula excludes Saturdays and Sundays?

Use NETWORKDAYS.INTL to count working days and WORKDAY.INTL to calculate the next working date. For a Monday-to-Friday schedule, use the weekend pattern "0000011". For example, =NETWORKDAYS.INTL(B5,C5,"0000011") counts weekdays between the two dates.

How do I exclude holidays as well as weekends?

Place holiday dates in a range, such as $J$2:$J$20, then add that range to your formula. For example, =NETWORKDAYS.INTL(B5,C5,"0000011",$J$2:$J$20) excludes both Saturday and Sunday, plus every holiday listed in the range.

Why does my Friday-to-Monday task show four days?

Your duration calculation is probably counting calendar days. A formula such as =C5-B5+1 includes Saturday and Sunday. Replace it with NETWORKDAYS.INTL, which counts only the working days defined by your weekend pattern.

Should I use a text axis or a date axis?

Use a text axis when each visible label represents one working-day category and you want a quick visual result. Use a weekday-only helper timeline when the chart must accurately represent task positions, working durations, holidays, and schedule changes.

Conclusion

To remove weekends from an Excel Gantt chart, create a weekday-only timeline, calculate offsets with NETWORKDAYS.INTL, calculate task lengths with working-day formulas, and format the chart carefully.

But here's the truth: hiding Saturday and Sunday labels is only half the job. The underlying values must also exclude nonworking days, or your schedule can still show incorrect durations.

For a small project, five formula-driven steps are usually enough. For complex workflows, recurring approvals, restricted-network planning, or frequent schedule changes, a project platform such as ONES.com can reduce manual chart maintenance.