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.
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.