Make an Excel Gantt Planner Count Weekdays Only
Question details
The user needs an Excel Gantt planner to count and display task durations in weekdays rather than calendar days.

- 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.
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.
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.
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.
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.
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.
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.

Exclude holidays or use a different weekend
Use WORKDAY.INTL when your workweek differs from Monday through Friday or you need to skip holidays.
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. 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. 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. 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. 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.

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.




