Traffic Light System

H Hill

Hi, I'm trying to set-up a traffic light formula with the following data field: Review Meeting Minutes QRD1406, The parameters are:

1. If TODAY is within 365 days from Review Meeting Minutes QRD1406 is then Green
2. If TODAY is between 366 to  547 days from Review Meeting Minutes QRD1406 is then Amber
3. If TODAY is > 548 days from Review Meeting Minutes QRD1406 is then Red 

Can anyone please let me know how the formula should be typed? I keep on getting "unparseable".

Thank you

Paul Newcome

I would add it as another nested IF at the start.

=IF([Column Name]@row = "", "false", IF(………

The above will check if it is a blank. If you want it to only run if it is a date (as there could potentially be some other data type in there, you would use:

=IF(NOT(ISDATE([Column Name]@row)), "false", IF(………………………….

Paul Newcome

Happy to help. 👍️

Paul McGuinness

Hi @H Hill

Are you typing the Rag in the formula as "Green", "Yellow" and "Red" or using "green" or green in the formula will return that error.

If not that cause then try this:

=if(Today()-[Review meeting minutes QRD1406]⇐365,"Green",If(Today()-[Review meeting minutes QRD1406]>=548,"Red","Yellow")

Hope that helps

Paul McGuinness
Central Operations Manager at Care UK.

H Hill

Hi Paul,

Thanks, I tried your suggestion (see below) and I still get the same error -

"unparseable"

What am I missing?

Thanks

Paul Newcome

The "less than or equal to" isn't being output properly. You will need to retype that directly in Smartsheet.

You have

⇐

You need

<=

You also need to specify row numbers after column names. If you are applying this as a column formula, you would use @row.

You have

[Column Name]

You need

[Column Name]@row

H Hill

Hi Paul,

Thank you, I am still new to smartsheets so still learning, that has worked now, one last question what do I need to add if the column does not have a date add the word false? and where do I need to add it in the formula?

Thank you

Paul Newcome

I would add it as another nested IF at the start.

=IF([Column Name]@row = "", "false", IF(………

The above will check if it is a blank. If you want it to only run if it is a date (as there could potentially be some other data type in there, you would use:

=IF(NOT(ISDATE([Column Name]@row)), "false", IF(………………………….

Paul McGuinness

Thanks @Paul Newcome goes to prove don't try and write formulae on the fly while heading out the door.

@H Hill glad it was resolved for you.

Paul McGuinness
Central Operations Manager at Care UK

H Hill

Hi Paul & Paul,

Thank you so much for your help on this, it all now works as required.

I really appreciate the support on this topic.👍️🙌

Paul Newcome

Happy to help. 👍️