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
alansidman
Well-known Member
- Joined
- Feb 26, 2007
- Messages
- 8,414
- Office Version
- 365
- Platform
- Windows
How about posting some sample data and a mocked up solution for 8-10 records. Please use XL2BB and no pictures as we cannot manipulate data in pictures.
Upvote
0
Table below. I am looking for the workday function syntax that would return the number of workdays in the list.
| Book1 | |||||
|---|---|---|---|---|---|
| C | D | E | |||
| 3 | Date | ??? | |||
| 4 | 12/18/2024 | ||||
| 5 | 12/19/2024 | ||||
| 6 | 12/20/2024 | ||||
| 7 | 12/21/2024 | ||||
| 8 | 12/22/2024 | ||||
| 9 | 12/23/2024 | ||||
| 10 | 12/24/2024 | ||||
| 11 | 12/25/2024 | ||||
| 12 | 12/26/2024 | ||||
| 13 | 12/27/2024 | ||||
| 14 | 12/28/2024 | ||||
| 15 | 12/29/2024 | ||||
| 16 | 12/30/2024 | ||||
| 17 | 12/31/2024 | ||||
| 18 | 1/1/2025 | ||||
Sheet1 | |||||
Upvote
0
alansidman
Well-known Member
- Joined
- Feb 26, 2007
- Messages
- 8,414
- Office Version
- 365
- Platform
- Windows
I am confused. What two dates are you comparing? Your request is lacking information necessary to respond.
Upvote
0
The table has a series of dates. Let's assume that they are in ascending order. The table will at times be filtered to show only 1 day or a subset of days. I am looking for a formula for outside of the table that would essentially combine a Subtotal function with the Networkdays function or something else to that effect.. It would give workdays for the displayed data either filtered or unfiltered.
| Book1.xlsx | |||||
|---|---|---|---|---|---|
| C | D | E | |||
| 3 | Date | ??? | |||
| 4 | 12/18/2024 | ||||
| 5 | 12/19/2024 | ||||
| 6 | 12/20/2024 | ||||
| 7 | 12/21/2024 | ||||
| 8 | 12/22/2024 | ||||
| 9 | 12/23/2024 | ||||
| 10 | 12/24/2024 | ||||
| 11 | 12/25/2024 | ||||
| 12 | 12/26/2024 | ||||
| 13 | 12/27/2024 | ||||
| 14 | 12/28/2024 | ||||
| 15 | 12/29/2024 | ||||
| 16 | 12/30/2024 | ||||
| 17 | 12/31/2024 | ||||
| 18 | 1/1/2025 | ||||
Sheet1 | |||||
Upvote
0
Hi vanwooten,
I use a similar formula for counting the remaining number of student Test Days… which only fall on weekdays.
This should get you started considering you mentioned you'll be using this formula outside of a table. However, since the dates are in the Table, you'll use Table references to count the dates. It shouldn't matter where you place this formula in your Workbook, it will return the correct number of days. If you want it inside of a Table, it's best suited in the Totals Row, else you'll have a column with all the same values.
Note: The Holiday switch is optional. If you have a list of dates that are Holidays, or some general blackout dates, just add that range into the Holiday portion of the formula so it doesn't count those dates.
| VBA Testing.xlsm | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Date | Day | ||
| 2 | 2024-12-18 | WED | ||
| 3 | 2024-12-19 | THU | ||
| 4 | 2024-12-20 | FRI | ||
| 5 | 2024-12-21 | SAT | ||
| 6 | 2024-12-22 | SUN | ||
| 7 | 2024-12-23 | MON | ||
| 8 | 2024-12-24 | TUE | ||
| 9 | 2024-12-25 | WED | ||
| 10 | 2024-12-26 | THU | ||
| 11 | 2024-12-27 | FRI | ||
| 12 | 2024-12-28 | SAT | ||
| 13 | 2024-12-29 | SUN | ||
| 14 | 2024-12-30 | MON | ||
| 15 | 2024-12-31 | TUE | ||
| 16 | 2025-01-01 | WED | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B16 | B2 | =UPPER(TEXT(A2,"ddd")) |
| VBA Testing.xlsm | |||
|---|---|---|---|
| A | |||
| 18 | n Days | ||
| 19 | 11 | ||
| 20 | |||
| 21 | Start Date | ||
| 22 | 2024-12-18 | ||
| 23 | |||
| 24 | End Date | ||
| 25 | 2025-01-01 | ||
| 26 | |||
| 27 | Formula: | ||
| 28 | =NETWORKDAYS(start_date, end_date, [holidays]) | ||
Sheet1 | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A19 | A19 | =NETWORKDAYS(MIN(t_Trades[Date]),MAX(t_Trades[Date])) |
| A22 | A22 | =MIN(t_Trades[Date]) |
| A25 | A25 | =MAX(t_Trades[Date]) |
Best regards,
Upvote
0
Thank you for this. It is the solution for the entire column. I wonder if anyone will come up with a way to the count the networkdays from a filtered data set.
Upvote
0
