Date within Next 5 Business Days



I wanted to create either a checkbox or an indication of a date being the next 5 business days in order to pull values in that row into a separate metric sheet for data based on "this business week"

So for instance, if a due date is either today or within 5 days from now, I would like to check a box:

=IF([Due date]@row <= TODAY(5), 1)

which works but will pull all dates in the past as well, so I improvised to a range:

=IF(AND([Due date]@row <= TODAY(5), [Due date]@row >=TODAY(), 1)

which results in #incorrect argument. After trying a number of variations of this I created a workaround using NETWORKDAY instead of using a checkbox:

=NETWORKDAY(TODAY(), [Due date]@row)

then only pulled the values that were in the 0-5 range (only positives) for things like status, revenue, etc:

=COUNTIFS({Due date range}, 0 >= 5, {Status}, [Primary Column]5)

which pulled ALL future dates, not just in my range. I ended up going with WEEKNUMBER(TODAY()) but could someone help me to understand why none of the above would pull only values within my range of 0-5 days from today?

Thank you!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!