Automating RGYB Balls With Date Ranges

I want the status balls to automatically update based on the due date - but I also want the status to go blue after the "Item Completed" column is checked.

Ie.

If End Date is in the past, or due within the next week = Red

If End Date is Due within the two weeks = Yellow

If end Date is Due in three weeks or more = Green

If "Item Complete" is Checked = Blue

I currently have a working formula, but it is only based on past, today, and future - not on date ranges. (See image.)

Status Formula.JPG

Find more posts tagged with

Construction

Formulas

Comments

Mike Wilday

Try this one:

It's written for row 23, so adjust the row number as needed. I changed the requirements of Green so that if its less than the criteria for yellow, then it will display green. Let me know if it works.

=If([Item Complete]23 = 1, then "Blue", IF([End Date]23 >= Today(-7), "Red", IF([End Date]23 >=Today(-14), "Yellow", IF([End Date]23 <=Today(-14), "Green"))))

Nicole Stewart

Mike, Thanks for your help! I'm getting an unparseable error.

I had changed a column name since my post yesterday, but I changed it back to match the formula.

Status Formula 4-17 1.24PM.JPG

Nicole Stewart

Actually, Mike. It works perfectly! There was a "then" that I had to remove from the formula. However, I have discovered that I need one more command.

IF End Date is blank, then red. I tried using "ISBLANK", but got an unparseable error.

Mike Wilday

Whoops. I see that then in there... Try this one: U added an OR statement.

=If([Item Complete]23 = 1, "Blue", IF(OR(ISBLANK([End Date]23), [End Date]23 >= Today(-7)), "Red", IF([End Date]23 >=Today(-14), "Yellow", IF([End Date]23 <=Today(-14), "Green"))))

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?