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!

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!