RYG Formula
Good Day All:
I am trying to add to my formula so that if the Due Date (Actual) is Blank/Empty, the RYG is Blank/Empty. My existing formula:
=IF([Due Date (Actual)]3 > [Due Date (Planned)]3, "Red", IF([Due Date (Actual)]3 < [Due Date (Planned)]3, "Green", IF([Due Date (Actual)]3 = [Due Date (Planned)]3, "Blue")))
Thanks
Comments
-
You are so close...just add a comma and two speech marks after the "Blue" setting before the closed brackets.
eg
=IF([Due Date (Actual)]3 > [Due Date (Planned)]3, "Red", IF([Due Date (Actual)]3 < [Due Date (Planned)]3, "Green", IF([Due Date (Actual)]3 = [Due Date (Planned)]3, "Blue","")))
The ,"" on the end means else leave blank. Your formula checks for being greater than, less than and equal to, the only other condition that would meet true on this is if there isn't a date in Due Date (Actual)3. With an IF function the final part can be a FALSE option, which we read as otherwise do this. So in your scenario if it isn't greater than, less than or equal to the planned date then you want the ball to be left empty. I believe it will do the trick for you.
Hope this helps.
Kind regards
Debbie
-
One thing to keep in mind with Debbie's solution...
Empty date cells are considered less than a populated date cell. I can't remember how far back the earliest possible date is that's been programmed for, but a blank date cell is basically saying the date at the beginning of time.
I generally use one of two different ways to account for this (it all depends on what happens to come out of the keyboard - no real preference or advantage to either).
.
1. =IF(ISBLANK([Due Date (Actual)]@row), "", rest of your formula)
.
2. =IF(ISDATE([Due Date (Actual)]@row), rest of your formula)
-
Thanks Debbie and Paul
-
-
I have one more formula that should be easy but for some reason it is displaying a Up Arrow instead of Sideways when % Complete Plan = % Complete Actual.
Formula:
=IF([% Complete (Actual)]3 > [% Complete (Planned)]3, "Up", IF([% Complete (Planned)]3 > [% Complete (Actual)]3, "Down", IF([% Complete (Planned)]3 = [% Complete (Actual)]3, "Sideways")))
-
How are your % Completes being calculated? It could be that hidden decimal places are making the difference.
-
The % Complete Plan has a formula that tells me what % the task should be on a daily basis.
=IF(AND(TODAY() >= [Start Date (Planned)]3, TODAY() <= [Due Date (Planned)]3), (NETWORKDAYS([Start Date (Planned)]3, TODAY()) / Duration3), IF(TODAY() > [Due Date (Planned)]3, 1, 0))
The Actual % is numeric with no decimal
-
Paul:
I copied % Complete Planned Cell to % Actual, and it does have hidden decimals:
66.6666666666667%
Is their a way to remove the hidden decimals in the formula? I guess the easiest way is to carry the decimals out in the %Complete (Actual) Column which would cause unnecessary questions and issues.
However, I can Round the %Complete (Planned) Formula which I am unsure how to add that to the existing formula.
Your thoughts?
-
If you provide your existing formula, we should be able to work it into that for you.
-
Existing Formula:
=IF(AND(TODAY() >= [Start Date (Planned)]5, TODAY() <= [Due Date (Planned)]5), (NETWORKDAYS([Start Date (Planned)]5, TODAY()) / Duration5), IF(TODAY() > [Due Date (Planned)]5, 1, 0))
-
=IF(AND(TODAY() >= [Start Date (Planned)]5, TODAY() <= [Due Date (Planned)]5), ROUND(NETWORKDAYS([Start Date (Planned)]5, TODAY()) / Duration5, 2), IF(TODAY() > [Due Date (Planned)]5, 1, 0))
Give this a try...
-
Excellent, Thank you Paul!
-
-
Paul:
I found an error in my Process for Task Status. I am trying to add to my formula, but receiving UNPARSEABLE.
1.Less than 100% actual complete can be considered ahead of schedule if it is greater than Planned
2.100% Actual Complete Done
3. 0% Actual Complete Not Started
Formula:
=IF(AND([% Complete (Actual)]3 > [% Complete (Planned)]3, [% Complete (Actual)]3 < 1, "Ahead", IF([% Complete (Planned)]3 > [% Complete (Actual)]3, "Behind", IF(AND([% Complete (Planned)]3 = [% Complete (Actual)]3, [% Complete (Actual)]3 <1 "On Track", IF([% Complete (Actual)]3 = 1, "Complete", IF([% Complete (Actual)]3 = 0, "Not Started"))))
Thanks
-
It's just a couple of missing parenthesis and commas.
Here you go...
=IF(AND([% Complete (Actual)]3 > [% Complete (Planned)]3, [% Complete (Actual)]3 < 1), "Ahead", IF([% Complete (Planned)]3 > [% Complete (Actual)]3, "Behind", IF(AND([% Complete (Planned)]3 = [% Complete (Actual)]3, [% Complete (Actual)]3 <1), "On Track", IF([% Complete (Actual)]3 = 1, "Complete", IF([% Complete (Actual)]3 = 0, "Not Started")))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
