gantt Excel formula Help

[Terry Gillespie]

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.

[#2]

Hey Terry-

have you taken a look at this Gantt Planner Template yet?  Will it do what you would like?

https://templates.office.com/en-us/Gantt-project-planner-TM02887601

[#3]

I did and this is the type of thing I am looking for, however I need the formula to incorporate it into a project sheet that is already being used. I cant seem to acquire the formulas to help me get on the right track. Thank you for your help.

[#4]

Terry-

Would it be possible for you to provide a non-sensitive example file to help me under stand the structure of your data?  A simple Before and After example may help as well.  If I understand correctly you want to use conditional formatting to color in the cells based on a start and end date?

If you can't provide a file maybe a few screenshots would suffice?

Coincidentally, I found this template several years ago and had to modify it to get the result I needed.  I think I have a pretty good idea of what you would like to see. An example will make testing your specific scenario a bit easier.

[#5]

Did you receive the example?

[#6]

Terry-

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

[#7]

Here it is.  It came back to me the other way I sent it.  

[#8]

Can you open it?

[#9]

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.

[#10]

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

[#11]

Terry-

Maybe try using several conditional formatting formulas for different percent complete colors like this:

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

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

etc...

Please see attached .xlsx file for reference.

[#12]

Did I tell you that I am using a Mac.  I am having trouble finding the same places to put the formulas.

[#13]

Try looking at these links for more guidance on how to access conditional formatting on Macs.  You want to select the option that allows you to use a formula :

https://support.office.com/en-us/article/use-a-formula-to-apply-conditional-formatting-in-excel-2016...

https://support.office.com/en-us/article/highlight-patterns-and-trends-with-conditional-formatting-e...