Gantt-Conditioning Format

[MyCon]

Hi,

I created a simple Gantt chat to show Planned vs. Actual Between August, 2015 to March, 2016, as follows:

Green Bar = Planned = Conditional Formatting Function
=AND($C10<F$2,TODAY(),$D10>=E$2)

Blue Bar = Actual = Conditional Formatting Function: =AND($C11<F$2,TODAY(),$D11>=E$2)

My date criteria is in Rows 2 & 3

For Actual, I'm looking to modify, so that my Blue bar does not go to the end where I have "Pending" dates because the tasks is still being worked.

How to modify, =AND($C11<F$2,TODAY(),$D11>=E$2) to not exceed current date?

Change file extension from ".doc" to ".xls"

See file example

Thanks

 

[MyCon]

Hi,

Would an If / Or type of function work?

Just trying to show the Blue or Actual status bar within current month if task is still pending.


Thanks

 

[Chihiro]

You need 2 sets of condition. See attached.

 

[MyCon]

Hi Chihiro,

This is great!! - Thanks

 

[MyCon]

Hi Group,

I would like to expand on my Gantt Conditioning formats.

Before, I had stacked Gantt bars, which work great - Thanks to the assistance here.

Now, I would like to have % complete status bar & overlapping Gantt bars.

In the attached file on the Overlapping Gantt, I started a new template for myself. I need assistance on the "Pending" status bars & how to add in % status bar.

Thank you

 

[Chihiro]

Instead of CF method you may want to go with Chart method.
Since, CF can only be approximation.

Say for example, you have project with 2 month duration, and have status of 65% complete. CF will apply to either first month only or both the first and the second month. Depending on the logic used.

Alternately you can build Gantt Chart based on CF, using more granular time measurements (ex. each cell representing single day etc).

See links for tutorial on how to create Gantt Chart.
http://peltiertech.com/Excel/Charts/GanttChart.html
http://chandoo.org/wp/2009/10/06/project-status-dashboard/

 

[MyCon]

Hi Chihiro,

Thanks for replying.

I can see how presenting the % Complete bar can be difficult or inaccurate with my template setup. However, I did see this done somewhere a while a go.

At this time, I'm more interested with getting Work In Progress or "Pending" bar working & if there is a completed date, that bar be hidden & the "Finish" bar appearing.

How is this doable?

Is there a way to nest these 2 functions?

Actual
'=AND($E16<I$2,TODAY(),$F16>=H$2)

Work In Progress
=AND($E16<I$2,$F16="PENDING",F$16<=TODAY())

Thanks

 

[Chihiro]

See attached. To prevent Pending item from showing up on completed items, you want to set "Stop If True" on "Completed" CF and order your conditions accordingly.



Formula for all CF has been modified slightly.

One thing, you don't need ,Today(), part as it's not doing any logical check.

 

[Siddharth Spehia]

I team,


I have created a gantt chart by taking a help from chandoo.com.

It is a very basic chart.

Please help me for following points:

I want to added few thing into this, which i am not able to do.

I wanted to colour actual progress over planned and it should be based on %.

Not able to figure out the formula and conditional formatting .

I am attaching the excel which i have made.

 

[Chihiro]

I'd recommend posting in new thread. Instead of piggy backing on old thread.

At any rate, if you want to show % completion along with schedule. I'd recommend going with Chart method, instead of Conditional format method.

See link I posted earlier for how to build it.
http://peltiertech.com/Excel/Charts/GanttChart.html