Conditional Formatting Based on Dates &/or Percentage

[mycon73]

Hi,

Attached is an example file that I submitted sometime ago & got some great assistance.

Before, I was looking to get conditional formatting based on date inputs. Now, I'm looking to add through a % Complete input.

In the attachment, you'll see 2 worksheets


First Worksheet: Stacked Gantt

How to get the % Complete option to produce a conditional format?


Second Worksheet: Overlapping Gantt


Right now, I have 2 conditional formats based on date inputs. I wish to add the % Complete option to produce a conditional format.

Thanks for the assistance.

[Green Crocodile]

Hi,

How do you want the percentage to factor into the conditional formatting?

As in do something if a certain percentage is reached? Do you want it to be a condition on it's own or do you want it to be a part of one of the other conditions you have? Can you please give an example of how it should behave?

[mycon73]

Hi Green Crocodile,

Thanks for responding.

Here's what I think what I want.

Based on actual durations worked or standard work week (Mon. - Friday / net workdays), have a third & thin bar or symbol going across which would be controlled through the % Complete input.

Right now, this would be a manual or estimated input by the user selecting a % drop down option. However, it would be nice if it could be some type of calculated input based factoring the standard work week but I think this would get too complex. Is either of these doable?

Right now, I just have random % percents in with some showing 100% with Actual Finish Date. Is there way to correlate that if I don't have an actual finish date, then I must enter a % input?


For tasks that show "Pending", would require % Complete should be simple enough.

Thanks

[mycon73]

Hi Group,

Can anyone assist?

How can I have a conditional function that is controlled by % incomplete between desired dates?

In this instance, it's either between date stated & current date or date task finished

Is this doable?

Thanks

[Green Crocodile]

Hi,

What you are saying is doable. We can create a separate condition to do this. When you say based on a standard workweek kind of have me two directions of how you want the chart to show.

Utilizing the days can show how far along the project is. Like it is supposed to take 20 days, we are at day 10 it says ok it is 50% complete. However, keep in mind that this might not reflect the actual completed phase of a project. During my PM days we often ran into where the project was not complete by deadline. As in we expected to be halfway point by day 10, but for whatever reason the team was only been able to deliver 30%.

This is just a recommendation, but maybe have both? One line showing the completion based on days, and the other showing the actual. I'm on my ipad right now and don't have access to Excel, but when I get home (if its not too late), or tomorrow I will try to come up with something.

[mycon73]

Hi Green Crocodile & Others,

For this purpose - I'm assuming a standard work week is Monday through Friday. However, the full work week is Monday through Sunday but we typically don't work on the weekends.

As for your example 20 days to represent 50% if halfway through the duration would be ideal if the project was progressing like that.

For my purpose, it's a manual estimate at this time.

So, if I have a planned task start at the beginning of the month & plan to complete with 30 days, how can I represent the % compete within this duration?

Perhaps, you can profile example using my my example file & for each worksheet.

Thanks

[Green Crocodile]

We can set it up to show the completion not by a month or workdays but between beginning and ending days.

So say P1 starts at 01/01/2017 and anticipates end date of 01/04/2017. 2 days past the start will be 50%. P2 starts at 02/01/2017 and plans to end on 07/31/2018; 50% would be when they hit Nov 30.

We can set it up as such. Everything is possible as long as we know where we are headed.

Please let me know if im on the right track of understanding your needs? Also, please bare with me... It is somewhat difficult for me to explain in writing (English third language). Im getting better though, moved to US.

I hope ipe that made sense.

[mycon73]

Hi Green Crocodile

Please provide your example file.

You kind of lost me with my own example file.

Thanks

[mycon73]

Hi Green Crocodile

Please provide your example file.

You kind of lost me with my own example file.

Thanks