Formula Needed

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?

Itai Perez

Hey @Rod Sanderson ,

=IF(OR(AND(Status@row <> "Complete", [End Date]@row <= TODAY()), AND(Status@row = "in progress", [End Date]@row >= TODAY()), AND(Status@row = "in progress", [Start Date]@row <= TODAY())), "Red", 0)

I think this should work for the red.

Let me know if this is the direction you are looking for and I can write the rest of the colors as well.

Itai Perez

Hey @Rod Sanderson ,

=IF(OR(AND(Status@row <> "Complete", [End Date]@row <= TODAY()), AND(Status@row = "in progress", [End Date]@row >= TODAY()), AND(Status@row = "in progress", [Start Date]@row <= TODAY())), "Red", 0)

I think this should work for the red.

Let me know if this is the direction you are looking for and I can write the rest of the colors as well.

Rod Sanderson

Wow such a quick response - that looks like exactly what I am looking for - if you could do it for the other colors I will drop it into sheet and run it! THANK YOU!

Rod Sanderson

I placed this in my Smartsheet and it works for some of Red except it does not change to Yellow if the status in 'In progress" and between the "Start Date" and "End Date" - Also if the project is "In progress" before the "Start Date" it also goes Red, and will you be able to build for Yellow and Green too please.

Rod Sanderson

Hi Itai did you see my response. It appears that the RED is not reacting appropriately and I was hoping you could help me to build the formula for the Yellow and Green?