count of working days to include current month

[DDT123]

Greetings, I have a table and needing to know the count of working days in a week but, to exclude days which fall into a different month from the weeknum. Our office is open Monday thru Saturday, so if the begin date falls on a Monday, there's potentially 6 working days in that week.

Example: week number 22 of year 2023 was May 29 thru June 4. There were 3 working days in week 22 which were in May.

I've included a screenshot of what the table should look like, but needing the formula in the first column.

 

[steve the fish]

And where is excel being told you are only interested in May?

 

[Dave Patton]

Try the following for June and adapt one of the alternatives to your data

T202306a.xlsm
	C	D	E	F
1	June	01-Jun-2023	30-Jun-2023	26
2
3	Mon 29-May-23	Sat 01-Jul-23		26
4
5	30			26
6
2e

Cell Formulas
Range	Formula
F1	F1	=NETWORKDAYS.INTL(D1,E1,11)
F3	F3	=NETWORKDAYS.INTL(MAX(C3,D1),MIN(E1,D3),11)
F5	F5	=C5-NETWORKDAYS.INTL(D1,E1,"1111110")

 

[DDT123]

And where is excel being told you are only interested in May?

This is for billing purposes. The company sends us a bill for the 1st thru 15th and the 16th thru the 31st of each month. I'm needing to break it down by week number when I'm doing an audit, so it should exclude the 1st thru 3rd of June, 2023 since those dates will be on the next billing cycle.

 

[DDT123]

Try the following for June and adapt one of the alternatives to your data

T202306a.xlsm
	C	D	E	F
1	June	01-Jun-2023	30-Jun-2023	26
2
3	Mon 29-May-23	Sat 01-Jul-23		26
4
5	30			26
6
2e

Cell Formulas
Range	Formula
F1	F1	=NETWORKDAYS.INTL(D1,E1,11)
F3	F3	=NETWORKDAYS.INTL(MAX(C3,D1),MIN(E1,D3),11)
F5	F5	=C5-NETWORKDAYS.INTL(D1,E1,"1111110")

unfortunately none of those formulas worked

 

[Dave Patton]

The formula certainly works.
You can post an extract of your sheet with the forum's tool named XL2BB.
Please indicate your expected results.
We can then adapt the formula for you.

 

[DDT123]

The formula certainly works.
You can post an extract of your sheet with the forum's tool named XL2BB.
Please indicate your expected results.
We can then adapt the formula for you.

I'm unable to download/install external applications without an admin login.

In your above example =NETWORKDAYS.INTL(D1,E1,11) Try entering May 29, 2023 into cell C3 and June 4, 2023 into cell D3. The output is 6 days but the desired output is 3 days since June 1 thru 4 needs to be excluded due to being a different month.

 

[Joe4]

I'm unable to download/install external applications without an admin login.

In your above example =NETWORKDAYS.INTL(D1,E1,11) Try entering May 29, 2023 into cell C3 and June 4, 2023 into cell D3. The output is 6 days but the desired output is 3 days since June 1 thru 4 needs to be excluded due to being a different month.

Dave's formula seems to return 3 for me:

Are you sure you entered everything correctly?

 

[Dave Patton]

T202306a.xlsm
	A	B	C	D	E	F	G
1			June	01-Jun-2023	30-Jun-2023	26	15-Jun-2023
2
3			Mon 29-May-23	Sat 01-Jul-23		26
4
5			30			26
6
7			Mon 29-May-23	Sun 04-Jun-23		3
8			Mon 05-Jun-23	Sun 11-Jun-23		6
9			Mon 12-Jun-23	Sun 18-Jun-23		4	13
10			Mon 19-Jun-23	Sun 25-Jun-23		8
11			Mon 26-Jun-23	Sun 02-Jul-23		5	13
12
13			Thu 01-Jun-23	15-Jun-2023		13
14			16-Jun-2023	30-Jun-2023		13
15
2e

Cell Formulas
Range	Formula
F1	F1	=NETWORKDAYS.INTL(D1,E1,11)
F3	F3	=NETWORKDAYS.INTL(MAX(C3,D1),MIN(E1,D3),11)
F5	F5	=C5-NETWORKDAYS.INTL(D1,E1,"1111110")
G9	G9	=SUM(F7:F9)
F7:F8	F7	=NETWORKDAYS.INTL(MAX(C7,D$1),MIN(D7,G$1),11)
F9	F9	=NETWORKDAYS.INTL(MIN(C9,G$1),MIN(D9,G$1),11)
F10	F10	=NETWORKDAYS.INTL(MIN(C10,G$1+1),MIN(D10,E$1),11)
F11	F11	=NETWORKDAYS.INTL(MAX(C11,G$1),MIN(D11,E$1),11)
G11	G11	=SUM(F10:F11)
F13:F14	F13	=NETWORKDAYS.INTL(C13,D13,11)

 

[DDT123]

Dave's formula seems to return 3 for me:

View attachment 93209

Are you sure you entered everything correctly?

It worked. I didn't have the helper cells as in Dave's example