I'm new to smartsheet and going through (a lot of) various tutorials on YouTube. I picked from the Save Time And Increase Efficiency -which I'm obviously not good at- a formula that updates the STATUS column (green, yellow, red dots) according to closeness to the DUE DATE column, which is:

=IF([Due Date]2 < TODAY(+28), "Green",

IF(AND([Due Date]2 >+ TODAY(+14), [Due Date]2 < TODAY(+28)), "Yellow",

IF([Due Date]2 >= TODAY (+14), "Red")))

When used separately, say only the green or only the red, the formula works. As soon as I have the IF(AND...etc... an error message appears with UNPARSEABLE. I've looked at other responses but since many use different formulas and I'm totally new to Smartsheet, I wasn't able to apply the solutions to this one. Could anyone help me out please?

Hi Axel,

There were a few errors in the formulas.

Not sure how you want to put them together so here they are separate

Try something like this.

=IF([Due Date]@row < TODAY(+28); "Green")

=IF(AND([Due Date]@row > TODAY(+14); [Due Date]@row < TODAY(+28)); "Yellow")

=IF([Due Date]@row >= TODAY(+14); "Red")

The same version but with the below changes for your and others convenience.

=IF([Due Date]@row < TODAY(+28), "Green")

=IF(AND([Due Date]@row > TODAY(+14), [Due Date]@row < TODAY(+28)), "Yellow")

=IF([Due Date]@row >= TODAY(+14), "Red")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

One of the major issues I saw was a >+ together. I think it was meant to be a >=