Formula RYG automation with using Complete% and rest of date by end date.

komakoma

I'm trying to create Formula. It's almost completed. However, one last rule doesn't work.

Can someone suggest to modify my formula?

Currently the following conditions and formula is working fine.

- If Complete% is 100% = Green

- If Complete% is upper 60% = Green

- if Complete% is under 60% and Today is less 5 days before End date = Yellow

- if Complete% is under 60% and Today is less 3 days before End date OR behind End date = Red

- If Complete% is 0 = Gray

=IF(OR([% Complete]1 = 1, [% Complete]1 > 0.6), "Green", IF(AND([% Complete]1 < 0.6, [% Complete]1 > 0), IF(NETWORKDAYS(TODAY(), [End Date]13) < 5, IF(NETWORKDAYS(TODAY(), [End Date]1) <= 3, "Red", "Yellow"), "Green"), "Gray"))

but I tried to add one more rule, then it doesn't work, error said "UNPARSEABLE"

wanted to add one more rule is

- Even if complete% is upper 60%, Today is less 2 days before End date = Red

I added "AND" formula

=IF(OR([% Complete]1 = 1, (AND([% Complete]1 > 0.6, (NETWORKDAYS(TODAY(), [End Date]1) > 3)))), "Green", IF(AND([% Complete]1 < 0.6, [% Complete]1 > 0), IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, IF(NETWORKDAYS(TODAY(), [End Date]1) <= 3, "Red", "Yellow"), "Green"), "Gray")))

Please advise me. Thank you!

Find more posts tagged with

Formulas

IT & Operations

Project Management

Comments

Paul Newcome

Let's see if we can't simplify this a little bit and work in your extra criteria...

Working Formula:

Simplified Formula:

=IF([% Complete]1 > 0.6, "Green", IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow")), IF([% Complete]1 = 0, "Gray")))

Simplified Formula + New Criteria:

=IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [End Date]1) < 2, "Red", "Green"), IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow")), IF([% Complete]1 = 0, "Gray")))

komakoma

hi, Paul. Thank you so much for your kindly suggestion and created new formula. Appreciate that. Then, I checked it if it's working properly or not. Most rule does work.

but the below criteria for "Green" doesn't work well.

- Complete% gets 100% = "Green"

- Complete% is under 60% but today is over 5 days remaining before End date = "Green"

2nd criteria might be difficult to be added in current formula. But i wanted to add 1st one at least otherwise, all 100% completed task is going to be Red.

I tried to add criteria by using "OR". but my formula is wrong to work.

=IF((OR[% Complete]1 = 1, [% Complete]1 > 0.6), IF(NETWORKDAYS(TODAY(), [End Date]1) < 2, "Red", "Green"), IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow")), IF([% Complete]1 = 0, "Gray")))

Paul Newcome

=IF(([% Complete]1 = 1, "Green", IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [End Date]1) < 2, "Red", "Green"), IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow"), "Green"), IF([% Complete]1 = 0, "Gray"))))

My apologies. Give this a try...

komakoma

hi, Thanks for your support. Appreciate that.

But unfortunately that doesn't work still. "Unparseable" .

Yellow line was error that i pasted your new formula. Do you think if you still modify any by new idea? Thank you again for your help

Paul Newcome

That's my fault. I just copy/pasted your formula and made adjustments to the criteria. I completely missed a few extra parenthesis that had been tucked in.

=IF([% Complete]1 = 1, "Green", IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [End Date]1) < 2, "Red", "Green"), IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow"), "Green"), IF([% Complete]1 = 0, "Gray"))))

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…