I have a Trades table and it has a date column - Trades[Date]. The dates are sequential from top to bottom. Looking for a formula to use outside of the table that will return the number of workdays in the Date column.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Count workdays between the first and last dates in a table column.
- Thread starter vanwooten
- Start date
Excellent use of the Subtotal, Cubist.
I was trying the "filtered" data approach last night, but had no success. An online search results; AI proposed using NETWORKDAYS as usual, and the Subtotal in the Holidays portion of the formula. That was rejected by Excel of course.
I never even thought of using the Subtotal function versions of MIN and MAX. I learned something new and useful!
Upvote
0
Excel Facts
Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Looks perfect. Thank You.
Upvote
0
Cubist provided the perfect solution for you. See his post. I was testing it with your original Samle Data and it produces the correct results, even when you apply a filter to the Date column.
One thing to note is there are two versions of the function used: NETWORKDAYS and NETWORKDAYS.INTL
I live in the U.S., so use the former. The differences are exlained here: Excel WORKDAY and NETWORKDAYS functions to calculate working days
Best regards,
Upvote
0
Had to help my kid with a gift... and I come back to finalize my post... and you beat me to the punch!
Upvote
0
Looks like you got yourself a pretty solid solution. One thing I would recommend is formatting your list of blackout dates (Holidays) as a Table so you don't run into any issues as that list grows/shrinks.
| VBA Testing.xlsm | |||
|---|---|---|---|
| D | |||
| 1 | Date | ||
| 2 | 2024-12-24 | ||
| 3 | 2024-12-25 | ||
| 4 | 2025-01-01 | ||
Count | |||
EX: Holidays[Date]
Then you can update your formula from ... V6:V82 to...
Excel Formula:
=NETWORKDAYS.INTL(SUBTOTAL(105,Trades[Date]),SUBTOTAL(104,Trades[Date]),1,[I]Holidays[Date][/I])Attachments
Upvote
0
