Nest WORKDAYS from TODAY into IF formula to return correct symbol

Amanda Brixius

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!

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

Amanda Brixius

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

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

Amanda Brixius

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

What error are you getting?