RYG status set by % Complete and End Date

dhoch

I am using a Smartsheet template (Project Tracking Template) and it comes with RYGB statuses. I would like to only use RYG and adjust the formula but am failing miserably. Please help!!!

Here's what I'm trying to accomplish:

• Green = If % complete is 100% or if end date is greater than today 
• Yellow = If % complete is not 100% and the end date is within the next 7 days (in the future)
• Red = If % complete is not 100% and the end date has passed 

Any help would be appreciated!

Paul Newcome

Try this:

=IF(OR([% Complete]@row = 1, [End Date]@row> TODAY(7)), "Green", IF([End Date]@row< TODAY(), "Red", "Yellow"))

Paul Newcome

Try this:

=IF(OR([% Complete]@row = 1, [End Date]@row> TODAY(7)), "Green", IF([End Date]@row< TODAY(), "Red", "Yellow"))

JamesB

@dhoch

Try this:

=IF([% complete]@row = 1, "Green", IF(AND([End Date]@row <= TODAY() + 7, [End Date]@row >= TODAY(), [% complete]@row <> 1), "Yellow", IF(AND([End Date]@row < TODAY(), [% complete]@row <> 1), "Red")))

dhoch

Thank you, Paul! That worked.

Paul Newcome

@JamesB Your formula can actually be simplified quite a bit. If you keep in mind the nested IFs stop on the first true value, you can assume that to make it to the second IF then the first must be false, and to make it to the third IF then the first and second must be false.

The first place to simplify your formula using this logic is with both of your AND functions. You don't need to specify that the percent complete is not 1 because it is already assumed to not be 1 just by making it past the first IF statement.

You can also use this logic to re-order the IFs and get rid of the AND functions completely.

(there is also the part missing about if the percent is not 1 and the end date is more than 7 days in the future).

Paul Newcome

@dhoch Happy to help. 👍️

JamesB

@Paul Newcome The second if command in the formula accounts for three dependencies to be a yellow task. But thank you for shortening the formula. I look at a lot of your posts to give me ideas on formula creation. I do see where the formula I provided results in no color if all the statements are false.

Paul Newcome

@JamesB I did see that you are using 3 different arguments in the "Yellow" portion, but all three of them can actually be removed. The percentage using the "Green" logic being skipped and the two date pieces by going with "Red" being in the past as second.

By that logic, anything that makes it past both Red and Green must be less than 100%, must be greater than or equal to today, and must be less than or equal to today+7.

One thing I try to do when using nested IFs in RYG columns especially when we are looking at a range (whether that be dates, percentages, or whatever else) is establish what typically ends up being the red and green first since usually they are "anything over" and "anything under" then use the "value if false" portion to cover everything else with the yellow.

JamesB

@Paul Newcome I understand and agree completely. I usually start my formulas logically separated out and then marry them together. Thank you for you constructive comments.

dhoch

@Paul Newcome Thank you again for creating the formula. Some headers use the Blue symbol in the sheet, so I would like to start using it also. What would the formula be if I wanted to accomplish this? Let me know if the parameters don't make sense.

• Blue = If % complete is not 100% and the end date 7+ days in the future
• Green = If % complete is 100%
• Yellow = If % complete is not 100% and the end date is within the next 7 days (in the future)
• Red = If % complete is not 100% and the end date has passed 

Paul Newcome

@dhoch This is what I would use for that criteria set:

=IF([% Complete]@row = 1, "Green", IF([End Date]@row> TODAY(7), "Blue", IF([End Date]@row< TODAY(), "Red", "Yellow")))

It is pretty much the same idea as before. We do the % Complete first because once that reaches 100%, we don't care about the dates anymore (meaning % Complete is top priority). Then we do the Blue range of greater than 7 days followed by the Red range of less than today and say everything else (in between and not 100%) is Yellow.

dhoch

@Paul Newcome That worked, thank you very much! That saves me so much time.

Paul Newcome

Happy to help. 👍️