Nest WORKDAYS from TODAY into IF formula to return correct symbol

Options

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!

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!