How to stop highlight duplication of weeknum in second year of Gantt chart

[Minilin]

Hiya,

I have edited the Gantt chart template from Microsoft to highlight cells based on the week number rather than the date, as this is what our company uses for projects.

When a project runs over two years, the gantt chart then repeats itself , see image red circled areas. This has happened on other spreadsheets I have set up myself as well, is there a way of stopping this from happening?

Conditional formatting formula used: =AND($C10="Low Risk",I$9>=$F10,I$9<=$F10+$G10-1)
Formula on Gantt chart as well: =IF(AND($C12="Goal",K$9>=$F12,K$9<=$F12+$G12-1),2,IF(AND($C12="Milestone",K$9>=$F12,K$9<=$F12+$G12-1),1,""))

 

[Fluff]

The only way that I can think of is to use dates rather than just week numbers.

 

[Minilin]

The only way that I can think of is to use dates rather than just week numbers.

Ah that's a shame. It's a manufacturing company so dates aren't used, everything is based on week numbers.

 

[Fluff]

If all you have is week numbers, then excel has no way of knowing if it's week 43 of this year, next year or even the year after.

 

[Minilin]

If all you have is week numbers, then excel has no way of knowing if it's week 43 of this year, next year or even the year after.

I've found a way to get the year and the week number by using formula =YEAR(M7)&WEEKNUM(M7,21) and I have put the date in the box, as can be seen in the screenshot, with this formula below it.

Which is what I did before to get the Gannt chart working on just the week numbers. Now though the conditional formatting doesn't seem to like the 202141 that comes up.

 

[bebo021999]

If F10 is value "42", you can get the nearest DATE of week 42 from the left by: INDEX($I$7:$BK$7,MATCH($F10,I$9:$BK$9,0))
then replace F10 with DATE:
=AND($C10="Low Risk",I$7>=INDEX($I$7:$BK$7,MATCH($F10,I$9:$BK$9,0)),I$7<=INDEX($I$7:$BK$7,MATCH($F10,I$9:$BK$9,0))+$G10-1)

 

[Minilin]

Hiya,

I have edited the Gantt chart template from Microsoft to highlight cells based on the week number rather than the date, as this is what our company uses for projects.

When a project runs over two years, the gantt chart then repeats itself , see image red circled areas. This has happened on other spreadsheets I have set up myself as well, is there a way of stopping this from happening?

Conditional formatting formula used: =AND($C10="Low Risk",I$9>=$F10,I$9<=$F10+$G10-1)
Formula on Gantt chart as well: =IF(AND($C12="Goal",K$9>=$F12,K$9<=$F12+$G12-1),2,IF(AND($C12="Milestone",K$9>=$F12,K$9<=$F12+$G12-1),1,""))

I actually managed to resolve the issue myself by adding a secondary column for the year and then converting the Week number and year into the corresponding date. I then based the conditional formatting off of the date.