0

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

Improve this question
1
  • 3
    It will not be highlighted because 18 is indeed not in between 22 and 24.
    – L42
    Commented Apr 12 at 12:12

2 Answers 2

Reset to default
2
+100

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)
Improve this answer
3
  • 3
    Thanks for your solution, swapped the ; with , and worked well. I liked this one because it works for a period greater than a year.
    – Student201
    Commented Apr 15 at 11:29
  • Could you also use Anydate-weekday(Anydate;3) where the days are numbered from zero starting at Monday?
    – Tom Sharpe
    Commented Apr 19 at 8:10
  • 1
    @TomSharpe Yes, indeed. And that would be a shorter formula. The only reason I'm using Weekday(anydate;2) it's because in my country the daily lifestyle implies Monday=1, but this is just math so yes, you can use Anydate - Weekday(anydate;3)
    – Foxfire And Burns And Burns
    Commented Apr 19 at 8:20
1

Do you have acces to ISOWEEKNUM ?

Start with selecting the range, make sure the first cell in the row is active. (You could do this for the entire chart I think)

=AND(ISOWEEKNUM(R$13)>=MIN(ISOWEEKNUM($C13:$D13)),ISOWEEKNUM(R$13)<=MAX(ISOWEEKNUM($C13:$D13))

Formula is applied to the selected range, which in this case would only be cell R13; But if you decide to select the entire row, make sure to change all the instances of 13 by whatever cellnumber your row starts at.

Hope it helps! Let me know if you run into an issue.

Screenshot of results

I started with selecting C3 through F6, with C3 as the active cell (important for the relative references)!

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.