RYG Formula

Tony Barthelemy

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

 

Debbie Sawyer

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

Paul Newcome

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)

Tony Barthelemy

Thanks Debbie and Paul

Paul Newcome

Happy to help! 

Tony Barthelemy

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")))

Paul Newcome

How are your % Completes being calculated? It could be that hidden decimal places are making the difference.

Tony Barthelemy

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

Tony Barthelemy

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?

 

Paul Newcome

If you provide your existing formula, we should be able to work it into that for you.

Tony Barthelemy

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))

Paul Newcome

=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...

Tony Barthelemy

Excellent, Thank you Paul!

Paul Newcome

Happy to help! 

Tony Barthelemy

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

Paul Newcome

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")))))