Health status formulas and automation

Titilope Laniyan

Hello anybody,

Please help me with automation formulas for the Health column? I'm very new to Smartsheet and struggling a bit! I'm trying to do the following:

Red flag - when the date has passed

Yellow fag - when the date has not passed

Thank you

Genevieve P.

Hi @Titilope Laniyan

Yes, this is definitely possible!

All you need to do is to say that if the date in the "1st Delivery Date" column is in the past, the column should show "Red". Otherwise (if the date is Today or in the Future), then show "Yellow".

Try this:

=IF([1st Delivery Date]@row < TODAY(), "Red", "Yellow")

Keep in mind that blank cells will be read as "in the past" which means that if the "1st Delivery Date" is blank it will be Red.

Let me know if this works for you!

Cheers,

Genevieve

Sameer Karkhanis

To do this you will need additional requirements (fields to be considered) than just the date coz date alone is not enough to decide when it is Yellow or Green (and Blue or Grey if you are using 4 lights).

Bob Kernan

Hi Titilope,

I have the same question. Can someone help us? What additional fields are needed and what would the formula be?

Thanks@

Titilope Laniyan

Can you tell us what additional requirement do we need?

Genevieve P.

Hi @Titilope Laniyan and @Bob Kernan

Are you able to post a screen capture of your sheet set up so we can see your columns/column types (but please block out any sensitive data), and can you define exactly when you want each colour to appear?

The way to build this formula is to add a number of IF statements together in a long "nested IF" formula, but we need to know what the formula is looking for, first.

For example, you're wanting the ball to turn red if a date in a date column is older than today, is that correct? That would be this IF statement:

=IF([Date Column]@row < TODAY(), "Red"

Then what colour should it be when that date IS today? Yellow?

IF([Date Column]@row = TODAY(), "Yellow"

And then if it's in the future, you could do Green:

IF([Date Column]@row > TODAY(), "Green"

Add them together:

=IF([Date Column]@row < TODAY(), "Red", IF([Date Column]@row = TODAY(), "Yellow", IF([Date Column]@row > TODAY(), "Green")))

Does that make sense?

Cheers,

Genevieve

Titilope Laniyan

Capture 1.JPG

Here is the screen capture of the sheet.

My goal is whenever the 1st delivery date column is updated. when the date passed- turn to red flag and Yellow flag - when the date has not passed.

I don't know if that's possible

Thank you

Genevieve P.

Hi @Titilope Laniyan

Yes, this is definitely possible!

All you need to do is to say that if the date in the "1st Delivery Date" column is in the past, the column should show "Red". Otherwise (if the date is Today or in the Future), then show "Yellow".

Try this:

=IF([1st Delivery Date]@row < TODAY(), "Red", "Yellow")

Keep in mind that blank cells will be read as "in the past" which means that if the "1st Delivery Date" is blank it will be Red.

Let me know if this works for you!

Cheers,

Genevieve

Titilope Laniyan

Thank you. This was helpful and the formula worked

Genevieve P.

No problem! I'm glad I could help. 🙂