Conditional formatting issue start and end dates

Liverlee

Board Regular
Joined
Nov 8, 2018
Messages
73
Office Version
  1. 2019
Platform
  1. Windows
Hello, can someone help me with the conditional formatting formula for this Gantt chart. It's driving me up the wall.
I've 12 monthly dates formatted as mmm-yy they increment with edate formula.

I've tried this but it seems to start highlighting from January through to the correct end date
=AND($D6="T", OR(AND(INDEX($H$4:$S$4,1,COLUMN()-COLUMN($H$6)+1)=TEXT($E6,"mmm-yy"), INDEX($H$4:$S$76,MATCH($E6,$E$6:$E$76,0),COLUMN()-COLUMN($H$6)+1)<=$F6), AND(INDEX($H$4:$S$4,1,COLUMN()-COLUMN($H$6)+1)<=$F6, $E6<=$F6)), COLUMN()>=COLUMN($H6), COLUMN()<=COLUMN($S6))

and I've tried this =AND($E6="T", $F6<=INDEX($H$4:$ZL$4,1,COLUMN(F6)-COLUMN($F$6)+1), $G6>=INDEX($H$4:$ZL$4,1,COLUMN(F6)-COLUMN($F$6)+1)) some months it works for i.e if start and end date in the same month.

Pleeeeeeeeeeeeeease can someone save a soul and give me a solution.
 

Attachments

  • gantt pic.png
    gantt pic.png
    12.7 KB · Views: 17

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
How about
Excel Formula:
=AND($D6="T",$E6<=H$4,$F6>=H$4)
 
Upvote 0
it's almost perfect. For some reason, if it's a date that isn't the 1st jan or 1st feb like 15th Jan it won't pick up the jan but show feb-through to whatever end date.

I've put in 2 helper columns to pick up eomonth dates for the start and end dates to get the 1st of the month and will use your code with new helper columns in G and H.

Thankyou folks
 
Last edited:
Upvote 0
Ok, how about
Excel Formula:
=AND($D6="T",EOMONTH($E6,-1)<H$4,$F6>=H$4)
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top