Forum Discussion
Conditional formatting with weeknumbers
For our planningscheme I use: a startdate, duration and endweek (Y-axis), and the weeks of the year (X-axis). If the weeknumber in the X-axis is within the start and endweek it needs te be coulored. This far I am able to do this.
The problem starts when the planning goes to the next year. From that point the weeknumbers start over from 1. But my created formula only understands the colouring until week 52. How do I need to make the conditional formatting to show all the right colours according to our planning?
Hi Randy,
Perhaps the easiest way is to add helper row (#3) with sequential week numbers independently of the year (but that works only for the projects starting this year with end on this/next year). When the rule is (see attached as well)
=AND(G$3>=$D4,G$3<=($D4+$E4),$D4<>"")
If your planning is for several years when better to work with actual calendar and/or together with Start Week column use one more mentioning at which year the project starts adding 51 to the Start Week of each next year.
