RYG Automation. The Y is the Kicker!

TLee

Hello everyone,

I'd like to ask for your help with a specific automation I'd like to create for the RYG circle status indicators for tasks. My status definitions are as follows:

1. Green means On Schedule = Which is the default color when none of the other statuses are true
2. Red means Late = % Complete is less than 100% and the task end date has passed
3. Blue means Complete = % Complete is equal to 100%

Yellow means Behind Schedule and this is the tricky one. I want to define this as a combination of the number of days of duration that have elapsed and the percent that should be accomplished by intervals in the duration. My intervals are 25%, 50% and 75%. So for example, if a task has an 8 day duration, then once 2 days (25% of 8) have passed during the duration, then % Complete should be at least 25% or on the third day of the 8 day duration the green circle would automatically change to yellow. Once 50% of the duration (4 days) has passed, it would change to yellow on the 5th day if % Complete is not at least 50%. Once 75% of the duration (6 days) has passed, it would change to yellow on the 7th day if % Complete is not at least 75%. On the 9th day, if % complete is not 100%, then the status would change to Red and it would be late.

 

I figured out a formula for the blue, red and green statuses and it seems to work . . .

 

=IF([% Complete]18 = 1, "Blue", IF(AND([% Complete]18 < 1, TODAY() > [End Date]18), "Red", "Green"))

 

Here is what I came up with for Yellow and it is not working . . .

 

=IF(AND([% Complete]25 < 0.25, TODAY() > [Start Date]25 + Duration25 * 0.25, TODAY() <= [Start Date]25 + Duration25 * 0.5, "Yellow", "Green", IF(AND([% Complete]25 < 0.5, TODAY() > [Start Date]25 + Duration25 * 0.5, TODAY() <= [Start Date]25 + Duration25 * 0.75), "Yellow", "Green", IF(AND([% Complete]25 < 0.75, TODAY() > [Start Date]25 + Duration25 * 0.75, TODAY() <= [Start Date]25 + Duration25), "Yellow", "Green"))))

 

I'd like to combine both formulas into one formula that works based on the definitions I described so I can use it in the status column to automatically flip the RYG circles. Do you think there is a syntax issue with the formula for Yellow? Any help you can provide would be much appreciated!

 

Torrey

 

Paul Newcome

There are a few issues throughout the formula, but it seems like you are going down the right path here.

 

First...

Your AND statements are not being closed. You have

 

IF(AND(this is true, this is true, this is true, then do this..........

 

but it should be

 

IF(AND(this is true, this is true, this is true), then do this..........

 

Note that we are closing the AND statement to use it as our logical statement before moving on to the "if true" portion of the IF.

.

Second...

You are not following correct syntax for nesting IF statements. I noticed you did it correctly in your first formula though.

You have

 

IF(this is true, do this, otherwise do this, IF(this is true, do this, otherwise do this))

 

but it should be

 

IF(this is true, do this, IF(this is true, do this, otherwise do this))

.

Because you are repeating ""Yellow", "Green"", we can actually consolidate a little bit. Let's start by writing it out before we build the formula.

 

Yellow Conditions:

[% Complete]25 < 0.25 AND TODAY() > [Start Date]25 + Duration25 * 0.25 AND TODAY() <= [Start Date]25 + Duration25 * 0.5

OR

[% Complete]25 < 0.5 AND TODAY() > [Start Date]25 + Duration25 * 0.5 AND TODAY() <= [Start Date]25 + Duration25 * 0.75

OR

[% Complete]25 < 0.75 AND TODAY() > [Start Date]25 + Duration25 * 0.75 AND TODAY() <= [Start Date]25 + Duration25

 

Green Conditions:

None of the above is true.

.

So looking at the above, we can actually say for Yellow 

 

"If this AND statement or this AND statement or this AND statement are true, then Yellow.

 

Which can also be written as

 

=IF(OR(this AND, this AND, this AND), "Yellow", "Green")

=IF(OR(AND(..........), AND(..........), AND(...........)), "Yellow", "Green")

=IF(OR(AND([% Complete]25 < 0.25, TODAY() > [Start Date]25 + Duration25 * 0.25, TODAY() <= [Start Date]25 + Duration25 * 0.5), AND([% Complete]25 < 0.5, TODAY() > [Start Date]25 + Duration25 * 0.5, TODAY() <= [Start Date]25 + Duration25 * 0.75), AND([% Complete]25 < 0.75, TODAY() > [Start Date]25 + Duration25 * 0.75, TODAY() <= [Start Date]25 + Duration25)), "Yellow", "Green")

TLee

Thanks Paul!

Yesterday, I combined all of this into one formula for red, blue green and yellow and did some testing. It seems to be working for all colors except for a few scenarios with yellow. Here is the formula I have. Do you see any errors here?

=IF([% Complete]1 = 1, "Blue", IF(AND([% Complete]1 < 1, TODAY() > [End Date]1), "Red", IF(OR(AND([% Complete]1 < 0.25, TODAY() > [Start Date]1 + Duration1 * 0.25, TODAY() <= [Start Date]1 + Duration1 * 0.5), AND([% Complete]1 < 0.5, TODAY() > [Start Date]1 + Duration1 * 0.5, TODAY() <= [Start Date]1 + Duration1 * 0.75), AND([% Complete]1 < 0.75, TODAY() > [Start Date]1 + Duration1 * 0.75, TODAY() <= [Start Date]1 + Duration1)), "Yellow", "Green")))

TLee

I inserted a task and made the duration 7 days with the end date being today and % complete is 30%. But the ball is still green and should be yellow since by today the task should be at least 75% complete.

I also inserted a task with a 2 day duration with the end date being today. The task should be at least 50% today for the ball to be green, but it changes to green when I put in 25% complete.

 

 

TLee

Here's screenshots.

TLee

I really appreciate your help on this! I've been adjusting the last formula I posted since yesterday to try and get it to work in all scenarios. This will be so huge for our PMO if we can get this working. 

 

Paul Newcome

I don't see any errors with the formula, but that doesn't necessarily meant that it is correct as far as a solution.

 

Try this troubleshooting...

 

Insert 3 date columns to the right of your start date.

 

In the first column enter

 

=[Start Date]@row + Duration@row * .25

 

Second column would be 

 

=[Start Date]@row + Duration@row * .5

 

and third would be 

 

=[Start Date]@row + Duration@row * .75

.

See if those dates are coming out as expected.

 

It may be that your idea and Smartsheet's idea of Duration@row * % is different.

Paul Newcome

In addition to the above...

 

Do you have dependencies enabled? If so, that could be where some of the issue is coming from. With dependencies enabled, the duration is in working days.

.

For example...

 

Start is 1 Oct

End is 22 Oct

.

Duration is calculated as 16d, but if you use

 

=[End Date]@row - [Start Date]@row, you get 21.375. Now... I am not sure where the fraction of a day is coming from, but even just the integers show a large difference.

 

25% of 16 is 4, but 25% of 21 is 5.25. Your display is showing the 16, but the calculations are running off of the 21.

.

Let me do some testing to see if I can get something working.

 

If you would like to share your email, I will share you to the sheet I am working on, so we can communicate more directly.

TLee

Thanks!

Paul Newcome

I have shared the sheet. Not sure how you felt about your email being in such a public forum, so I figured I'd let you know in case you wanted to go ahead and delete it from here.