@Shannon Heizenrader
I am creating a post for you so that your question and whatever solution is found is public. That way anyone having a similar issue can find help and other people are also able to weigh in on a solution that may be a better fit.
YOUR QUESTION:
Hi, Paul,I am new to the community and using formulas. I apologize if this is not the right way to ask my questions. I am looking to add a formula that is similar to one you helped with here:
Paul NewcomeApr 19, 2019
Let's see if we can't simplify this a little bit and work in your extra criteria...Working Formula: =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")) 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")))
This is the logic from the original formula with my changes. I am trying to replace the colored balls in the original with text - Completed, Future start, Active and on track, Active with some concerns, Active with major concerns. My date columns are titled Start and Finish.-If Complete% is 100% = Green - Completed - If Complete% is upper 60% = Green - Active and on track - if Complete% is under 60% and Today is less 5 days before End date = Yellow - Active with some concerns - if Complete% is under 60% and Today is less 3 days before End date OR behind End date = Red - Active with major concerns- If Complete% is 0 = Gray - Future start- Even if complete% is upper 60%, Today is less 2 days before End date = Red - Active with some concerns
I modified your formula to this:=IF([% Complete]1 = 1, "Completed", IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [Finish]1) < 2, "Active with major concerns", "Active and on track"), IF([% Complete]1 > 0, IF(NETWORKDAYS(Start(), [Finish]1) < 3, "Active with major concerns", IF(NETWORKDAYS(Start(), [Finish]1) < 5, "Active with some concerns"), "Active and on track"), IF([% Complete]1 = 0, "Future start"))))I am still getting an unparseable result. Can you help me figure out what I am missing?Thank you!Shannon
I will review your question and post my response in the comment section.
