Conditional Formatting for Gantt Chart not working

[e_nnm_m]

I am trying to create a gantt chart in excel using conditional formatting. For some reason, my formulas don't work for every event. I have two colors: dashed gray for uncompleted tasks, and red for completed tasks. The formula for the uncompleted tasks is:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'}</style>=AND(F$4>=$C8,F$4<=$D8)

The completed tasks: <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'}</style>=AND($C8<=F$4,$D8>=F$4,$E8=1)

The chart date is F$4 (in red, because this row will be hidden); the start date is $C8 and end date is $D8. You can see that these formulas don't work for tasks 1.2.3 and 1.2.4. Can you help me understand why?

I tried to insert a picture but couldn't, so here is the link:

https://photos.app.goo.gl/HLkW2wJSF2xYcD3i9

 

[Special-K99]

C8 and D8 are blank cells in that diagram.

 

[e_nnm_m]

Yes, but the range begins at F8. It works properly for 2 of the 4 rows. Actually, I have a whole sheet, and the two rows with short spans are the only two rows that don't work.

 

[Jon Peltier]

It would help to see what the dates are in the red cells. But it looks like the resolution of your grid for display dates is 3 months, and your resolution for start and end dates is days.

If your dates in L4:N4 are July 1, 2018, October 1, 2018, and January 1, 2019, none of them fall between C14 and D14 (December 1 and 12, 2019), so you will never shade those cells. You would need some kind of extended logic to detect if any portion of the task between start and end dates falls within the quarter indicated by the date in the header row.

 

[e_nnm_m]

The first is 1/1/17 and then =edate(f4,3) so 4/1/17...and so on.

 

[Jon Peltier]

I figured that's what it was. So you need to work on those algorithms.

 

[e_nnm_m]

Help with the algorithm or formula is why I posted this question in the first place.

 

[Jon Peltier]

Sorry, it was getting last last night, and I could barely think any longer. Maybe later...

 

[Jon Peltier]

You want to find out if a task begins in or before the quarter represented by column F, and ends in or after the quarter represented by column F, which happens if this formula is true:

=AND($C8<=G$4,$D8>=F$4)

For an unfinished task, you need this in the CF formula:

=AND($C8<=G$4,$D8>=F$4,$E8<1)

For a finished task, you need this:

=AND($C8<=G$4,$D8>=F$4,$E8=1)