Health column?

Bridget Jones

Hello,

I'm attempting to create a function in my "Health" column that will return Red, Yellow, or Green based on:

1) Status (dropdown input by sheet user)

2) Dates in the date columns to the right, broken down by step

We are attempting to track communications through various review processes. Some require legal review, and some do not. How do I make the health column reflect:

Green: Status Column is Not Started + Draft Due date is more than 7 days from today

Green: Status Column is Draft in Progress + Draft Due date is not more than 7 days from today

Green: Status Column is Fx Review in Progress + Fx Review Due date is in the future

Green: Status Column is Legal Review in Progress + Legal Review Due is in the future

Green: Status Column is Awaiting Distribution OR Distribution Complete + Distribution Date is in the future

Green: Status Column is Distribution Complete

Yellow: Status Column is Not Started + Draft Due date is 7 or fewer days from today

Red: Status Column is Not Started + Draft Due date is in the past

Red: Status Column is NOT Distribution Complete + Distribution Date is in the past

Yellow: Status Column is Draft in Progress + Draft Due date is in the past

Yellow: Status Column is Fx Review in Progress + Fx Review Due date is in the past

Yellow: Status Column is Legal Review in Progress + Legal Review Due is in the past

Yellow: Status Column is Awaiting Distribution + Distribution Date is in the past

Image 1-29-21 at 10.18 AM.jpeg

Thank you,

Bridget

Bridget Jones

IT WORKED! Thank you :)

Debbie Sawyer

Hi Bridget you will need to use logic similar to this:

=IF(OR(AND(Red Cond1,Red Cond1),AND(Red Cond2,Red Cond2)),"Red",IF(OR(AND(Yellow Cond 1,Yellow Cond1),AND(Yellow Cond2,Yellow Cond2),AND(Yellow Cond3,Yellow Cond3),AND(Yellow Cond4,Yellow Cond4),AND(Yellow Cond5,Yellow Cond5)),"Yellow","Green"))

where

Red Cond1 = Red: Status Column is Not Started + Draft Due date is in the past

Red Cond2 = Red: Status Column is NOT Distribution Complete + Distribution Date is in the past

Yellow Cond1 = Yellow: Status Column is Not Started + Draft Due date is 7 or fewer days from today

Yellow Cond2 = Yellow: Status Column is Draft in Progress + Draft Due date is in the past

Yellow Cond3 = Yellow: Status Column is Fx Review in Progress + Fx Review Due date is in the past

Yellow Cond4 = Yellow: Status Column is Legal Review in Progress + Legal Review Due is in the past

Yellow Cond5 = Yellow: Status Column is Awaiting Distribution + Distribution Date is in the past

Use [status]@row="Not Started" etc

Use [Draft Due date]@row<TODAY() for in the past.

Let me know if this helps :)

Good luck

Debbie

Bridget Jones

IT WORKED! Thank you :)

Debbie Sawyer

Well done Bridget!

I hope you were ok with me giving you the logic instead of me writing it all out for you. Sometimes it is better for you to build the finished formula as you are more likely to understand what is going on and so will be able to write similarly complicated ones next time :)

Have a great weekend. Glad this helped.

Bridget Jones

I have a follow-up questions on this topic :).

I want to add a criteria that if status column = "distribution complete", the cell returns "blue". Where in the formula should I add that?

Thank you!

=IF(OR(AND(Status@row = "not started", [Distribution Date]@row < TODAY()), AND(Status@row <> "Distribution Complete", [Distribution Date]@row < TODAY())), "Red", IF(OR(AND(Status@row = "not started", [Draft Due]@row = TODAY(7)), AND(Status@row = "Draft in Progress", [Draft Due]@row < TODAY()), AND(Status@row = "Fx Review in Progress", [Review Due]@row < TODAY()), AND(Status@row = "Legal Review in Progress", [Legal Review Due]@row < TODAY()), AND(Status@row = "Awaiting Distribution", [Distribution Date]@row < TODAY())), "Yellow", "Green"))

Debbie Sawyer

Hi Bridget

I have popped it in the end between the "Yellow" and "Green" - i.e. before the final "otherwise" statement.

You need to ensure that the RAG balls selected in the column properties support a blue ball! :D

=IF(OR(AND(Status@row = "not started", [Distribution Date]@row < TODAY()), AND(Status@row <> "Distribution Complete", [Distribution Date]@row < TODAY())), "Red", IF(OR(AND(Status@row = "not started", [Draft Due]@row = TODAY(7)), AND(Status@row = "Draft in Progress", [Draft Due]@row < TODAY()), AND(Status@row = "Fx Review in Progress", [Review Due]@row < TODAY()), AND(Status@row = "Legal Review in Progress", [Legal Review Due]@row < TODAY()), AND(Status@row = "Awaiting Distribution", [Distribution Date]@row < TODAY())), "Yellow", IF(Status@row="Distribution Complete","Blue","Green")))

Let me know if this works for you!

Kind regards

Debbie

Bridget Jones

PERFECT! Thank you :). I had tried to do that, but think I didn't nest it correctly... I appreciate your help!

Bridget Jones

Debbie, you're so helpful - could you please help me with one other Smartsheet formula question? I'm attempting to have an "expected % complete" column, and have it working correctly using this formula

=IF([Start Date]@row < TODAY(), (TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row), 0)

However, I want the max return value to be 100% (I.e., not 145% if the finish date is in the past). How do I calculate that? I'm at a loss...

Thank you!

Debbie Sawyer

You're welcome

Bridget Jones

Hi Debbie, I see you're "you're welcome" message, but wasn't sure if you see my other question? I think they were posted the same minute :) Thank you!

Debbie Sawyer

Hi Bridget, sorry like you said, our messages crossed, then I was in a client call! :D

Give me a mo and I'll test something out in a sheet - back in a few minutes

Bridget Jones

Thanks so much!

Debbie Sawyer

Here we go - I think this works...

=IF([Start Date]@row > TODAY(), 0, IF([Finish Date]@row < TODAY(), 1, ((TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row))))

Bridget Jones

Apologies, I have a lot of questions today :). I'm also trying to make my "% Complete" column have the parent row the sum of the children rows - and not be able to be edited. So, the parent row would say "build website" and the child rows would have the steps to complete that task (e.g., hire developer, draft content, create logo). I want the task owners to update the "% Complete" column for the child rows, and I want the parent row in the % Complete column to summarize the % complete of the children rows - perhaps incorporating the length of each of the children tasks (I.e., if 1 task is 10 days and the other tasks are each 5 days, the first task represents 50% of the task). Is that possible? I have seen this before, but think I must be using the wrong type of column or something to enable this automatically? Thank you!

Debbie Sawyer

Bridget the Parent Row should automatically be summarising the children, is your sheet not doing it?

You can lock parent rows if you want to, there is a lock row option on the row menu.

If you have a moment, I can zoom and show you?

My email address is debbie.sawyer@smarterbusinessprocesses.com - might be easier!

Bridget Jones

Sure! I sent a zoom meeting invitation to you just now :) Thank you!