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
-
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"))
Answers
-
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?
-
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.
-
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?
" "
-
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.
-
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"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!