Date within Next 5 Business Days
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!
Best Answer
-
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)
Answers
-
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)
-
Thank you Paul!
-
Happy to help! 👍️
-
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
-
@KRH_SFO You need to incorporate a WORKDAY function.
=IF(AND([Due date]@row <= WORKDAY(TODAY(), 5), [Due date]@row >=TODAY()), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!