Traffic Lights & Dates

Options

i have a deadline, not actually in the smartsheet, of 30/06/22. I want a traffic light indication if my scheduled completion date , which is in the smartsheet, is after 30/06/22 to be RED, to be YELLOW if it is between 01/06/22 & 30/06/22 and GREEN if it is before 01/06/22?

Answers

  • Genevieve P.
    Options

    Hi @JOHN HONDARACER

    You can use the DATE Function to specify a date in an IF statement.

    The structure of DATE is DATE(YYYY, MM, DD)

    For example:

    =IF([Completion Date]@row > DATE(2022, 06, 30), "Red"

    Note that you will need to update the [Completion Date]@row column name with your current date column name. Then we just need to add together the other statements:

    =IF([Completion Date]@row > DATE(2022, 06, 30), "Red", IF([Completion Date]@row < DATE(2022, 06, 01), "Green", "Yellow"))

    We don't need to actually write out the Yellow statement. This is because we've already covered the dates after your end date and before your start date, so anything in the middle will be Yellow.

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

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!