Traffic Lights & Dates

JOHN HONDARACER

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?

Genevieve P.

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