How to Exclude Weekends in an Excel Gantt Chart Formula
Question details
The user wants to generate a sequence of dates for a Gantt chart based on a start date, automatically skipping weekends (Saturdays and Sundays).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a project management Gantt chart where timeline tasks only occur on weekdays.
- Observed behavior
- Need a formula to calculate and populate consecutive weekday dates starting from a specific project start date (cell E2) into a timeline row (starting in cell J4).
Ensure you have the project start date formatted correctly as a Date in your specified cell before applying the timeline formula.
Use the WORKDAY Function to Skip Weekends
The WORKDAY function calculates the nearest working day in the future or past, automatically excluding Saturdays and Sundays.
The WORKDAY function is ideal for project timelines and Gantt charts because it ensures that no tasks are scheduled on weekends. By combining it with the COLUMNS function, you can create a dynamic formula that increments the date automatically as you drag it across columns.
Identify the cell containing your project start date (e.g., cell E2) and select the cell where you want your timeline to begin (e.g., cell J4).
In cell J4, input the formula =WORKDAY($E$2-1,COLUMNS($J:J)). The $E$2 is an absolute reference to your start date, ensuring it remains constant as the formula moves.
Press Enter to apply the formula, then click and drag the fill handle (the small green square at the bottom-right of the cell) to the right to populate the rest of your Gantt chart dates.
If you want to exclude specific holidays, create a list of holiday dates in a separate range and add it as the third argument to your formula, like =WORKDAY($E$2-1,COLUMNS($J:J), $Z$2:$Z$10).
Create Professional Gantt Charts in WPS Spreadsheet
WPS Spreadsheet fully supports the WORKDAY function and advanced charting tools, making it incredibly easy to track project schedules, skip weekends, and manage complex timelines.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet document or open your existing project file.
- 2. Enter your project data: Type your project start date into a designated cell, such as E2.
- 3. Apply the WORKDAY formula: In your timeline row, enter =WORKDAY($E$2-1,COLUMNS($J:J)) and drag it across your Gantt chart area to generate weekday-only dates.
Frequently Asked Questions
How do I exclude custom holidays in addition to weekends?
You can add a list of holiday dates as the third argument in the WORKDAY function. For example, =WORKDAY($E$2-1, 1, A1:A10) where A1:A10 contains your specific holiday dates.
What if my work week doesn't have Saturday and Sunday off?
If your weekends fall on different days, use the WORKDAY.INTL function instead. It allows you to specify exactly which days of the week should be treated as non-working days using a custom weekend parameter.
Why is my formula returning a random number instead of a date?
Spreadsheet software stores dates as sequential serial numbers. If you see a number like 44200 instead of a date, select the cell, right-click, choose 'Format Cells', and change the number format to 'Date'.
How can I calculate the total number of working days between two dates?
To find the number of working days between a start and end date, use the NETWORKDAYS function. The formula =NETWORKDAYS(start_date, end_date) will automatically exclude weekends from the total count.
