# 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:

• ✭✭✭✭✭✭
Options

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", ""))))

• Options

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.

• ✭✭✭✭✭✭
Options

=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", ""))))

• Options

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?

• ✭✭✭✭✭✭
Options

What error are you getting?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!