Conditional Formatting for Gantt Chart not working

e_nnm_m

New Member
Joined
Oct 9, 2019
Messages
4
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
 

e_nnm_m

New Member
Joined
Oct 9, 2019
Messages
4
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
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

New Member
Joined
Oct 9, 2019
Messages
4
The first is 1/1/17 and then =edate(f4,3) so 4/1/17...and so on.
 

e_nnm_m

New Member
Joined
Oct 9, 2019
Messages
4
Help with the algorithm or formula is why I posted this question in the first place.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
Sorry, it was getting last last night, and I could barely think any longer. Maybe later...
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
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)
 

Forum statistics

Threads
1,078,342
Messages
5,339,652
Members
399,318
Latest member
kryten68

Some videos you may like

This Week's Hot Topics

Top