Calculation Issues

Make an Excel Gantt Planner Count Weekdays Only

Steven ParkerSteven Parker Nov 20, 2026 869 views

Question details

The user needs an Excel Gantt planner to count and display task durations in weekdays rather than calendar days.

Make an Excel Gantt Planner Count Weekdays Only
Product
Microsoft Excel Project Planner template
Device & OS
not provided
Scenario
Planning tasks in a Gantt chart where each duration should fill one visible cell per weekday.
Observed behavior
The template counts weekends even when their columns are hidden, so a task can fill fewer visible weekday cells than its duration.
Before you start

Check whether the template's timeline headers are daily dates or grouped periods, and identify the task start-date and duration columns. The example below uses daily headers, valid Excel dates, and positive whole-number durations.

Solution 1Recommended

Build a weekday-only Gantt grid with WORKDAY

Use a daily grid when you want each duration unit to fill one Monday-through-Friday cell.

Hiding weekend columns does not remove those dates from the template's calculations. The example uses a weekday date sequence and a formatting rule that counts the start day as the first workday.

1
Set up the task columns

On a worksheet, enter the project timeline start date in A1. Put Start in B1 and Duration in C1, then enter each task's start date in column B and its weekday duration in column C, beginning on row 2.

2
Create weekday headers

In D1, enter =WORKDAY($A$1-1,1). In E1, enter =WORKDAY(D1,1), then drag E1 to the right for as many timeline days as you need. Format the header cells as dates using Home > Number.

3
Add the Gantt rule

Select the timeline cells, starting at D2, and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =IF(OR($B2="",$C2<=0),FALSE,AND(D$1>=WORKDAY($B2-1,1),D$1<=WORKDAY($B2-1,$C2))), choose a fill color, and click OK.

4
Check the timeline

Enter a task start date and a duration in B2 and C2. Confirm that the colored cells match the duration and that the date headers extend far enough to show the final workday.

Build a weekday-only Gantt grid with WORKDAY
Applying this to the template: If you adapt the Project Planner template, replace its daily period-date formulas and update its task and chart rules to reference the new dates. Reset any period controls as needed and check the chart range; simply hiding weekend columns will not change the calculations.
Plan tasks in WPS Spreadsheets

Create a Weekday-Only Gantt Grid in WPS Spreadsheets

Use WORKDAY formulas and conditional formatting in WPS Spreadsheets to display task durations as weekday cells.

  1. 1. Open a spreadsheet: Open WPS Spreadsheets and create a blank workbook or open an Excel workbook. Enter the timeline start date in A1, task start dates in column B, and durations in column C.
  2. 2. Add weekday dates: Enter =WORKDAY($A$1-1,1) in D1 and =WORKDAY(D1,1) in E1. Drag E1 across the timeline and format the headers as dates.
  3. 3. Color task durations: Select the timeline cells beginning at D2. Choose Home > Conditional Formatting > New Rule, select the formula-based rule, and enter =IF(OR($B2="",$C2<=0),FALSE,AND(D$1>=WORKDAY($B2-1,1),D$1<=WORKDAY($B2-1,$C2))). Choose a fill color and apply the rule.
  4. 4. Save the planner: Check that each task fills the expected number of weekday cells, then use File > Save As to save the workbook in XLSX format.
Build a timeline that skips weekendsOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceCreate and format a task grid for free
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't hiding weekend columns fix the Gantt chart?

Hiding columns changes only their visibility. The underlying dates and formulas still count weekends, so use a weekday date sequence and update the chart's formatting rules.

Does WORKDAY count the task's start date?

WORKDAY normally counts from the day after its start-date argument. Using WORKDAY(start_date-1,duration) includes a weekday start date as the first day.

What happens if a task starts on a weekend?

The example rule begins coloring on the next workday. For a Saturday or Sunday start under a Monday-through-Friday schedule, that is normally Monday.

How can I exclude public holidays too?

Enter holiday dates in a worksheet range and include that range as the holidays argument in both the timeline and task-end formulas. Use the same holiday range throughout the Gantt grid.