Gannt chart - conditional formatting not working as expected - wrong cell highlighted

[Minilin]

Hiya,

I have created a Gannt chart that highlights cells based on number of weeks effort entered next to a week number.
There appears to be an issue where it won't highlight the first cell the of the week the work needs to start in, it pushes it all up one week.

For example, in attached picture, Test 1,2,3 and 4 are due to start in week 47 for 5 weeks. The only way to get it to show to start in week 47 is to put it starting in week 46.

Conditional formatting formula used to highlight cells:
=AND($C6="Plan",M$4>=$F6,M$4<=$F6+$G6)

Unfortunately I am unable to upload a mini sheet as this is a company computer - cannot download anything.


Extra info in case helpful:
Formula used to convert Week Number and Year into date:
=DATE($E12,1,4)-WEEKDAY(DATE($D12,1,3))+$D12*7


At one point I was using the following formula for the conditional formatting
=AND($C6="Plan",M$4>=$F6,M$4<=$F6+$H6-1) based on the following formula in cell H =SUM($G12*7).

I cannot remember why I started doing it off of days instead of weeks, maybe it resolved the issue originally for me so I thought that was a fix.

 

[Fluff]

The reason the 1st week is not highlighted, is that M4 is less then F12.

 

[Minilin]

The reason the 1st week is not highlighted, is that M4 is less then F12.

Firstly - Hello again Fluff!

Ah ok, so that supports my theory that I need to get the dates to match!

Ones across the top do not match the ones that the formula I have used produces. As you can see M5 Week number for 22/11/2021 is Week 47 which is correct. The date in F12 given for Week 47 is 23/11/2021.

Do you know how I could fix the formula so they match?

 

[Fluff]

How about

=DATE(E12,1,-2)-WEEKDAY(DATE(E12,1,3))+D12*7

 

[Minilin]

How about

=DATE(E12,1,-2)-WEEKDAY(DATE(E12,1,3))+D12*7

That worked, thank you.

 

[Fluff]

You're welcome & thanks for the feedback.