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

Minilin

New Member
Joined
Oct 19, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Chart.png
    Chart.png
    52.3 KB · Views: 7

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
The reason the 1st week is not highlighted, is that M4 is less then F12.
 

Minilin

New Member
Joined
Oct 19, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=DATE(E12,1,-2)-WEEKDAY(DATE(E12,1,3))+D12*7
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top