Nest WORKDAYS from TODAY into IF formula to return correct symbol
Hello! I'm VERY new to this, but I worked out my first nested IF formula to pull symbols based on Start & End Dates.
=IF([Done?]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(3), "Yellow", IF([Start Date]@row <= TODAY(), "Green", ""))))
Logic...
- "Gray" IF task marked Complete
- "Red" IF End Date is less than TODAY (past due)
- "Yellow" IF End Date is 2 days greater than or equal to TODAY (3 days away past due; "At Risk")
- "Green" IF Start Date greater than or equal to TODAY (can begin work; not "At Risk")
My problem: my Yellow criteria includes weekend days even though my sheet settings are set for a regular 5 day workweek. How do I nest the WORKDAYS formula to represent Yellow as 3 workdays less than today?
Feel free to let me know if there's any other corrections I can make to this formula. I'm new to this and would not be surprised if I messed something up. Thanks!
Answers
-
You are going to want a NETWORKDAYS function.
=IF([Done?]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF(NETWORKDAYS([End Date]@row, TODAY()) <= 3, "Yellow", IF([Start Date]@row <= TODAY(), "Green", ""))))
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!
-
Thanks for the insight Paul! I tried to use this, but this made it so that everything greater than Today+3 was flagged as yellow, so that's where the formula is stopping for the entire column. I don't totally understand the difference between how I have it ordered and the way you did it, but mine still read through the entire formula.
I suspect I may end up needing an AND formula in there as well, where End Date is Today+3 AND Start Date is greater than or equal to Today. I've tried to write this out and I'm not getting any positive results.
-
Sorry about that. Had the wrong operator.
=IF([Done?]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF(NETWORKDAYS([End Date]@row, TODAY()) >= 3, "Yellow", IF([Start Date]@row <= TODAY(), "Green", ""))))
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!
-
Hi Paul. Sorry to bother again. When I plug this in, I get an error. Do you think using the AND function would help here?
-
What error are you getting?
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!