Health status formulas and automation

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

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Sameer Karkhanis
    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).

  • Hi Titilope,

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

    Thanks@

  • Can you tell us what additional requirement do we need?

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • Thank you. This was helpful and the formula worked

  • Genevieve P.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!