Conditional Formatting - Color scale change based on date and % completion

[hennakao]

Hi All

Hope someone can help with the following. I would like to use conditional formatting to do the following:

I have a list of open items that are due in the next 6 months. Each week the responsible stakeholders for the open items provide me a percentage completion status.

Risk Level Status Responsible Due Date %complete
High Open Team 1 31/12/2017 70%
Medium Open Team 1 30/11/2017 70%
etc....

In a pivot table I format all cells based on their percentage value with a 3-color scale. I want to refine this with a formula by saying that based on the due date and the percentage completion, the color scale might be different. For example, items due this month and a completion status of 70% should be red (as there are only 7 days left). Items due next month with a completion status of 70% should be amber (as there is still time left to complete them).

Would appreciate if someone can help me with this. Unfortunately cannot attach a sample file due to upload restrictions at my workplace.


Thank you!

 

[Chihiro]

I'd suggest you upload sample workbook with sanitized data.

Without it, we won't be able to give you specific formula that will suite your need.

 

[hennakao]

Apologies for the late response. I have uploaded a sample workbook with sanitized data. If anyone has an idea of how to solve this, please let me know.

Thank you!

 

[NARAYANK991]

Hi ,

I think a graded color scale cannot be combined with a formula ; I may be wrong.

However , we can always insert multiple CF rules using formulae , which can take into account any combination of the following fields :

Risk Level

Status

Due Date

Completion Percentage

If you can list down all the conditions , along with the colors that are to be used if each condition is satisfied , then all of them can be implemented.

Narayan

 

[Chihiro]

Applying CF on Pivot table (and keeping it consistent after Pivot table update) can be tricky, as any structural change on Pivot table may wipe part of applied CF.

One way to work around it is to apply CF via VBA code. Using .DataBodyRange of Pivot table.

 

[hennakao]

Applying CF on Pivot table (and keeping it consistent after Pivot table update) can be tricky, as any structural change on Pivot table may wipe part of applied CF.

One way to work around it is to apply CF via VBA code. Using .DataBodyRange of Pivot table.

Would you be able to show how to approach this via VBA code based on the data sheet i provided?

 

[Chihiro]

Sure but will need full scope.
Like what Narayan asked, and potential pivot manipulation such as filter/slicers etc

 

[hennakao]

Sure but will need full scope.
Like what Narayan asked, and potential pivot manipulation such as filter/slicers etc

Thanks for trying to point me in the right direction. Just to recap, I have provided a pivot in my sample workbook. Have decided not to use a Pivot, but will pull the data via Sumifs into a different tab. The layout will look the same as in the Pivot.

What I am trying to do now is the following:

IF Due date is in the next two months (e.g. November/December)
AND Risk Level is High, Medium OR Low
AND Status is Open
THAN
Red fill for % values between 10-50%
Amber fill for % values between 60-75%
Green fill for % values between 75-100%

Hope that does make sense.

 

[NARAYANK991]

Hi ,

Is this OK ?

Narayan

 

[hennakao]

Hi ,

Is this OK ?

Narayan

That is great! If i had to refine it based on the Risk level, how would i incorporate it? E.g. Only conditional format based on your solution for High, else colour it based on conditions xyz.

 

[NARAYANK991]

Hi ,

If the present formula is :

=E4 >= 75%

you can use :

=AND(E4 >= 75% , $C4 = "High")

Before you do that , you need to select the option Repeat Item Labels for this field , so that there are no blanks in this column.

Narayan