Formula help using RYG Balls

Patty Clark

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.

Paul Newcome

It helps to think of dates as numbers where TODAY() is zero.

TODAY(-5) would then be considered -5. You want to turn red if today (0) is between 0 and 5. So try changing TODAY(-5) to TODAY(5).

Within 10 days would be less than TODAY(10) for your yellow.

Both of those though are calendar days (not working days). For this you would need to incorporated a NETWORKDAYS function.

=NETWORKDAYS([Start Date], [End Date])

To incorporate this into your formula, it would look something like this...

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

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?

Patty Clark

Picture0002.jpg

 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

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?

" "

Patty Clark

Yellow_Green 2-24-20.jpg

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.

Paul Newcome

It helps to think of dates as numbers where TODAY() is zero.

TODAY(-5) would then be considered -5. You want to turn red if today (0) is between 0 and 5. So try changing TODAY(-5) to TODAY(5).

Within 10 days would be less than TODAY(10) for your yellow.

Both of those though are calendar days (not working days). For this you would need to incorporated a NETWORKDAYS function.

=NETWORKDAYS([Start Date], [End Date])

To incorporate this into your formula, it would look something like this...

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