# RYG Formula

Options
✭✭✭✭✭
edited 12/09/19

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

Tags:
«1

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭
Options

Thanks Debbie and Paul

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
edited 07/19/19
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Excellent, Thank you Paul!

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!