RYG Formula linked to Status and Start and End Date

Rod Sanderson

Hi Smartsheet Team, 

I am looking for help with a formula:

I have a column "Schedule Health" that has RYG Balls I am wanting this to change color based on certain criteria from 3 other columns "Status", "Start Date", "End Date".

Ball is GREEN if "Start Date" is 7 days from today (ie project has not started yet)

Ball is GREEN if "Status" is "Complete"

Ball is YELLOW if "Start Date" is 3 days from today

Ball is YELLOW if "Status" is "In Progress" and "Start Date" is in the future

Ball is RED if "Status" is "In Progress" and "Start Date" is today or past (Project running late)

Ball is RED if "Status" is "In Progress" and "End Date" is today or later (Project started but not completed on time)

Ball is RED if "Status" is anything other than "Complete" at "End Date" for today or late (No body has updated the project and its late to complete)

Can anyone help me please?

Paul Newcome

I understand what exactly you are asking for, and we can certainly write a formula for it, but I want to make sure what you have above is what you actually want.

For example, if the task in "In Progress" but the start date is in the future, wouldn't that mean it is actually ahead of schedule (you have that as "yellow")?

Status is In Progress, but the start date is in the past (but end date is still in the future). I wouldn't think the task would be "running late", but I could see having it turn yellow when the end date is within the next three days.

If you can confirm your post is in fact exactly what you want or make adjustments as needed, I'd be happy to help write out a formula to fit.

Rod Sanderson

Excellent Paul THANK YOU!

For example, if the task in "In Progress" but the start date is in the future, wouldn't that mean it is actually ahead of schedule (you have that as "yellow")?

You know I agree now - lets keep that GREEN as Yellow should really alert us that the item needs attention shortly. Good call!

Status is In Progress, but the start date is in the past (but end date is still in the future). I wouldn't think the task would be "running late", but I could see having it turn yellow when the end date is within the next three days.

Good question on the logic here - so if the project is "In Progress" that should be GREEN as it has started and still within its time frame to complete - once it gets close to the End Date we need the line to warn us - thereby yellow - once it gets past END Date it should be RED. I hope this makes sense!

THANK YOU for your help!

Paul Newcome

How does this sound for logic?

Green:

Start Date in future (regardless of status)

Status is In Progress and End Date is more than 3 days in the future

Status is Complete

Yellow:

Status is Not Started but Start Date has passed with end date more than 5 days in the future

Status is In Progress and End Date is less than 3 days in the future (but not passed)

Red:

Status is Not Started and End Date is within the next 5 days

Status is NOT Complete and End Date has passed

If the above sounds good to you, we can use this:

=IF(OR(Status@row = "Complete", [Start Date]@row> TODAY(), AND(Status@row = "In Progress", [End Date]@row> TODAY(3))), "Green", IF(Status@row = "Not Started", IF([End Date]@row>= TODAY(5), "Yellow", "Red"), IF([End Date]@row< TODAY(), "Red", "Yellow")))

Rod Sanderson

Paul this is EXCELLENT - I have not dropping into my sheet to test as I am on the road for a little bit.

Can we tweak logic just a little:

Yellow:

Status is Not Started but Start Date has passed with end date more than 5 days in the future

Can we change that to - Status is Not Started but Start Date is 3 days away with End Date more than 5 days in the future - That way it says "Hey this is Start date is coming due in 3 days and your should be paying attention."

THANK YOU AGAIN!

Paul Newcome

Give this a try:

=IF(OR(Status@row = "Complete", [Start Date]@row> TODAY(3), AND(Status@row = "In Progress", [End Date]@row> TODAY(3))), "Green", IF(Status@row = "Not Started", IF([End Date]@row>= TODAY(5), "Yellow", "Red"), IF([End Date]@row< TODAY(), "Red", "Yellow")))

Rod Sanderson

Again Thank you PAUL! What a blessings. you really build such a slick formula! You should have seen the 20 lines I made to try do the same thing ... will drop it in and see.

Thank you again!!

Paul Newcome

Happy to help. 👍️

Don Wood

This almost does what I want, except if the status is complete I want the color to be gray, regardless of dates.

Thanks!

Paul Newcome

@Don Wood Yours would look more like this at the beginning:

=IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row> TODAY(3), AND(Status@row = "In Progress", [End Date]@row> TODAY(3))), "Green", IF(Status@row = "Not Started", IF([End Date]@row>= TODAY(5), "Yellow", "Red"), IF([End Date]@row< TODAY(), "Red", "Yellow")))

Mlindblom

Paul, I was also able to cut and paste your formula for my sheet. Thank you! You saved me a lot of frustration and time.