Date within Next 5 Business Days

C. Powell

Hello,

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!

Paul Newcome

The reason your second formula of IF/AND did not work is because you need to close out the AND statement before moving on to the next part. It is just a missing closing parenthesis after the TODAY function.

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

For the COUNTIFS, you would want to specify less than or equal to 5 and greater than zero.

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

Paul Newcome

The reason your second formula of IF/AND did not work is because you need to close out the AND statement before moving on to the next part. It is just a missing closing parenthesis after the TODAY function.

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

For the COUNTIFS, you would want to specify less than or equal to 5 and greater than zero.

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

C. Powell

Thank you Paul!

Paul Newcome

Happy to help! 👍️

KRH_SFO

Hi @Paul Newcome

Your answers and explanations are always SO helpful!

For this one in particular, is there a way to have the box ticked if [Due Date] is in the next 5 working days? I need to be able to exclude weekends from the validation.

Thanks so much,

Kurt

Paul Newcome

@KRH_SFO You need to incorporate a WORKDAY function.

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