Forum Discussion

Terry Gillespie's avatar
Terry Gillespie
Copper Contributor
Jun 06, 2018

gantt Excel formula Help

I am looking for a formula that can take a start date and end date to calculate percentage then add a color to the grid below the date.
Formulas & Functions
Need Help
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jun 07, 2018

Terry-

 

I have not received an example.  Did you post it to this thread?  I didn't see a Private Message either.

  • Terry Gillespie's avatar
    Terry Gillespie
    Copper Contributor
    Jun 11, 2018
    Can you open it?
    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jun 12, 2018

      Hi Terry-

       

      I'm attaching an example file, that should help get you to your end goal.  You may need to make several different conditional formatting rules based on your specific needs.  In the example you can see how to color the cells a specific way based on date criteria.  If you would also add in that the task needs to be complete you would just add an additional evaluation to the AND() statement that checks to see if that cell is at 100%.

       

      Here is the conditional formatting formula:

      =AND(I$9>=$D11,I$9<=$F11)

      It is applied to:

      $I$11:$AL$15

       

      Please note I also changed that weekday abbreviations to actual dates to assist the conditional formatting formula (they are formatted as DDD so they don't take up to much space...)

       

      Hope this helps.

       

       

      • Terry Gillespie's avatar
        Terry Gillespie
        Copper Contributor
        Jun 12, 2018

        Thank you so much.  I do have a question.  It that equation the additional (and), if so would I put it in the percent completed column?

         

        I so appreciate your help with this!

         

        Terry

Resources