Forum Discussion
Gantt Chart Weekday Function
I am trying to use a gantt chart with conditional formatting for a project. I have my conditional formatting set up the following: =AND($D9<=H$5, WORKDAY($D9-1, $C9)>=H$5) problem is, some of the ...
You’re running into two related Excel quirks at once:
- WORKDAY() only works with whole days
Any duration < 1 gets treated as 0, and when you subtract 1 you end up feeding -1 or -0.5 into the logic, which breaks your test. - Your -1 adjustment assumes at least a 1-day task
That’s fine for normal tasks, but not for same-day / partial-day tasks.
The fix…don’t subtract 1 unless the duration is ≥ 1 day
Recommended (cleanest) you can paste directly into Conditional Formatting.
=AND($D9<=H$5,WORKDAY($D9,MAX(0,$C9-1))>=H$5)
Alternative using IF
=AND($D9<=H$5,IF($C9<1,$D9,WORKDAY($D9,$C9-1))>=H$5)
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.