Search code examples
excelconditional-statementsgantt-chart

Gantt Chart on Excel - How to modify conditional formatting to work

I am trying to get my conditional formatting to work but failing.

In Row 13, the task starts 22/07/2022 and finishes 24/07/2022. This is in week beginning 18/07/2022, so I want this cell to be highlighted.

I have tried an AND statement as shown and it's not working as I hoped, as the cell hasn't highlighted.

Any help on how to change my formula would be appreciated.

enter image description here

Solution

  • enter image description here

    Your Gantt's chart is using MONDAYS as reference days, so your conditional formatting rule should make the same.

    But the task are not always referenced to mondays, so first you need a formula so given any date it will return always the last monday of same week:

    (Anydate-ABS(1-WEEKDAY(Anydate;2)) will return always a Monday. Now with this you can create a better CF rule:

    =AND(($A6-ABS(1-WEEKDAY($A6;2)))<=C$4;($B6-ABS(1-WEEKDAY($B6;2)))>=C$4)

    I'm using type 2 on WEEKDAY so Monday will be the first day of the week. Read documentation about it:

    WEEKDAY

    UPDATE: As suggested by @TomSharpe in comments, a different setup for Weekdate could be used to make a shorter formula, using type=3.

    =AND(($A6-WEEKDAY($A6;3))<=C$4;($B6-WEEKDAY($B6;3))>=C$4)