Conditional Formatting for Gantt Chart not working

e_nnm_m

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

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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

e_nnm_m

New Member
Joined
Oct 9, 2019
Messages
11
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,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
11

ADVERTISEMENT

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

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I figured that's what it was. So you need to work on those algorithms.
 

e_nnm_m

New Member
Joined
Oct 9, 2019
Messages
11

ADVERTISEMENT

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,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

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