Health Status Formula RYGG

Natasa5

Hello,

I am new to smartsheets and I am having difficulties setting up basic Health Formula with RYG+Gray bullets based on Status (Cancelled, Delivered) and Due date column.

GRAY when Status is Cancelled

RED when Status is Delivered, but after Due date

GREEN when Status is Delivered till and on Due date

YELLOW when none of the above applies

Could anybody help me out with this? Thank you!

Best,

Nataša

Genevieve P.

Hi @Natasa5

Welcome! 🙂 I'd be happy to help you with your formula.

The one thing I will note is that it sounds like you may need a helper column in your sheet to identify when the "Delivered" status is changed. We can use a formula to check a date and compare it to Today's date, but not to a date when a change happened (unless that's recorded in the sheet somewhere).

I would recommend setting up a new Date Column titled "Actual Delivery Date" and then you can use a Record a Date workflow to automatically add the date that the status changes, but only when it changes to "Delivered". Then you can compare the date in your Actual date column to the Due Date column.

Now we'll use your exact instructions, even in that exact order, to create your formula.

First statement:

=IF(Status@row = "Cancelled", "Gray"

^ Notice how I use @row after the column name. This tells the formula to look in the cell of that column in this current row where the formula is being built. Then I have to put any text in "quotes".

Next statement:

IF(AND(Status@row = "Delivered", [Due Date]@row < [Actual Delivery Date]@row), "Red"

^ Here we need to use an AND to indicate it's only Red if both of these are true. The less than symbol < checks if the Due Date is in the past compared to the Actual date.

Next statement:

IF(Status@row = "Delivered", "Green"

^ Here we don't have to check the dates because we already did that in the previous statement. the formula will only continue to read if the previous Red statement is false, meaning that the due date is NOT in the past.

Final statement:

"Yellow"

FULL FORMULA:

=IF(Status@row = "Cancelled", "Gray", IF(AND(Status@row = "Delivered", [Due Date]@row < [Actual Delivery Date]@row), "Red", IF(Status@row = "Delivered", "Green", "Yellow")))

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Natasa5

Welcome! 🙂 I'd be happy to help you with your formula.

The one thing I will note is that it sounds like you may need a helper column in your sheet to identify when the "Delivered" status is changed. We can use a formula to check a date and compare it to Today's date, but not to a date when a change happened (unless that's recorded in the sheet somewhere).

I would recommend setting up a new Date Column titled "Actual Delivery Date" and then you can use a Record a Date workflow to automatically add the date that the status changes, but only when it changes to "Delivered". Then you can compare the date in your Actual date column to the Due Date column.

Now we'll use your exact instructions, even in that exact order, to create your formula.

First statement:

=IF(Status@row = "Cancelled", "Gray"

^ Notice how I use @row after the column name. This tells the formula to look in the cell of that column in this current row where the formula is being built. Then I have to put any text in "quotes".

Next statement:

IF(AND(Status@row = "Delivered", [Due Date]@row < [Actual Delivery Date]@row), "Red"

^ Here we need to use an AND to indicate it's only Red if both of these are true. The less than symbol < checks if the Due Date is in the past compared to the Actual date.

Next statement:

IF(Status@row = "Delivered", "Green"

^ Here we don't have to check the dates because we already did that in the previous statement. the formula will only continue to read if the previous Red statement is false, meaning that the due date is NOT in the past.

Final statement:

"Yellow"

FULL FORMULA:

=IF(Status@row = "Cancelled", "Gray", IF(AND(Status@row = "Delivered", [Due Date]@row < [Actual Delivery Date]@row), "Red", IF(Status@row = "Delivered", "Green", "Yellow")))

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Natasa5

Thank you very much @Genevieve P.!

It works perfectly. Much appreciated!

Best,

Nataša