RYG Ball Automation

Hi there,

I am trying to automate the 'Health' column on a sheet. I have it set up to turn RYG when a certain status is selected but I would also like it to turn Red if the Due Date or Due for Review is a past date and the status is still listed as anything but 'Complete."

I currently have this formula in place

=IF(Status13 = "Not Started", "Red", IF(Status13 = "In Progress", "Yellow", IF(Status13 = "Complete", "Green", IF(Status13 = "Needs Edits", "Yellow", IF(Status13 = "Waiting For Review", "Yellow")))))

How would I add to it to make the date being in the past trigger a Red ball in the health column with the rest of the formula as well?

Find more posts tagged with

Construction

Formulas

Project Management

Comments

Mike Wilday

=IF(OR(Status13 = "Not Started", [Due Date Column Title]13 < today()), "Red", IF(Status13 = "In Progress", "Yellow", IF(Status13 = "Complete", "Green", IF(Status13 = "Needs Edits", "Yellow", IF(Status13 = "Waiting For Review", "Yellow")))))

Try this formula on row 13.

Change the Due Date Column Title to whatever the actual due-date column title is.

Paul Newcome

See if this covers what you need...

=IF(OR(AND(OR([Final Due Date]@row < TODAY(), [Due for Review]@row < TODAY()), Status@row <> "Complete"), Status@row = "Not Started"), "Red", IF(Status@row = "Complete", "Green", "Yellow"))

The first statement covers both of your red requirements of

1.a. Due Date OR Due for Review is in the past

and

1.b. Status is NOT Complete

2. Status is Not Started

The second IF covers the only reason for being Green (Complete)

After that you are showing everything else as Yellow, so instead of creating a bunch of IF's for each of those, you can just use "Yellow" in the third portion of the last IF statement (where you would put another IF statement), but instead we just tell it what to say if the first two IF's are false.

Paul Newcome

Looks like we were typing at the same time. Lol

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?