Traffic Lights & Dates

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

  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!