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

#### Minilin

##### New Member
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.

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
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
The reason the 1st week is not highlighted, is that M4 is less then F12.

#### Minilin

##### New Member
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
Excel Formula:
``=DATE(E12,1,-2)-WEEKDAY(DATE(E12,1,3))+D12*7``

#### Minilin

##### New Member
Excel Formula:
``=DATE(E12,1,-2)-WEEKDAY(DATE(E12,1,3))+D12*7``
That worked, thank you.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
96
Replies
6
Views
130
Replies
3
Views
270
Replies
0
Views
117
Replies
5
Views
122

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

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.

### Which adblocker are you using?

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

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