Formula help using RYG Balls

I have dependencies enabled on my sheet and working days set for Monday – Friday. I am trying to write a formula to change the ball color based on the Hard Deadline. If hard deadline is within 5 working days then red; hard deadline is within 10 working day then yellow; over 10 working days green. My formula returns #UNPARSEABLE

=IF([Hard Deadline]@row - TODAY() < -5), "Red", IF(And([Hard Deadline]@row - TODAY() < 5, "Yellow"), "Green")))

I’ve also tried this formula and get the same #UNPARSEABLE

=IF([Hard Deadline]@row <TODAY(-5), “Red”, IF([Hard Deadline]@row <=TODAY(5), “Yellow”, “Green”))

I am new to Smartsheet and formulas, so any assistance I can get would be greatly appreciated.

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your second formula should not be returning an error. Are you able to provide a screenshot of the sheet with sensitive/confidential data hidden, removed, or replaced with "dummy data" as needed? If possible, please be sure to include the column names and have a cell "opened" as if you are going to edit it so that we can see the formula as it is in the sheet?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Patty Clark
    Patty Clark ✭✭✭

     I attached a screen shot of the sheet I'm working on with the formula. There are 2 additional columns on the sheet: Comments and Ohio State Fair.

    Thank you for looking at this for me.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It looks like you are using "smart quotes" like what you find in Microsoft Word and other programs like it.


    Try either re-typing the formula directly into the sheet or using a program like Notepad.


    Notice how in your screenshot the quotes are kind of slanted and are very obviously open/close type quotes whereas even the quotes used when typing in the Community here are straight up and down and there is no difference between open/close quotes?


    " "

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Patty Clark
    Patty Clark ✭✭✭

    Thank you. Changing the quotes did take care of the error message; however, I still can't get the ball to appear red if deadline is within 5 days, green for all other dates. But I'm going to keep trying with the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!