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 tasks have a duration of less than 1 day (column C) and so adding the -1 is blanking out several tasks.
How do i correct this?
I'm just starting to learn conditional formatting so some of this is still like a foreign language to me.
Thanks!
2 Replies
Depends on logic. If duration 1 day always means next workday after the first worday (e.g. start is sat, first workday is Mon, duration =1, end = Tue which is Mon+1); 0 means start and end at first worday (e.g. for above is Mon), we may use
=($D9<=H$5) * (WORKDAY($D9-1, $C9)>=WORKDAY(H$5,-1) )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.
- WORKDAY() only works with whole days
