I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks: enter image description here

As you can see, the task bar currently highlights gray dependent on the start and end dates of the task using the formula =AND(I$4>=$E6,I$4<=$G6).

In non-cell specific terms: =AND(todaysdate$cell>=$startdatecell,todaysdate$cell<=$enddatecell)

I want to keep that function, but I want to add an aspect that will change that bar color dependent on if the progress percentage is 100%. (Essentially a visual way to show that task is complete vs. just scheduled.

Right now, all I can get is for the cells to change color up to those gray bars or after those gray bars, but I can't get the color to change in the gray bar itself. Any ideas on how I need to change my function to add this capability?

1 Answer 1


Update: I have found the answer. I split the above formula into two conditions as such:

=AND($C6=100%,I$4>=$E6,I$4<=$G6) then dark gray
=AND($C6<100%,I$4>=$E6,I$4<=$G6) then light gray

In non-cell specific terms: =AND($progresscell=value%,todaysdate$cell>=$startdatecell,todaysdate$cell<=$enddatecell) - shows completed task =AND($progresscell<value%,todaysdate$cell>=$startdatecell,todaysdate$cell<=$enddatecell) - shows scheduled but incomplete task

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.