Populating number of weeks days across multiple weeks between a date range

[ExcelNovice2017]

Afternoon all

The issue I'm having is populating the correct number of day under a working week especially when start / end date goes across months

What I'm looking to do is, add the number of days the data range covers and input that number in week 1,2,3 etc. each date range is a task and it can be owner by the same user or different user - I will then look to put this in a pivot chart to show where people within the team are under or over utilize per month

Thank you in advance for any help, would be massively appreciate

 

[JamesCanale]

You could also through in a list of holidays somewhere.

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			1/2/2023	1/9/2023	1/16/2023	1/23/2023	1/30/2023	2/6/2023	2/13/2023	2/20/2023	2/27/2023	3/6/2023	3/13/2023	3/20/2023
2	Start	End	1	2	3	4	5	6	7	8	9	10	11	12	Duration
3	1/2/2023	1/6/2023	5	0	0	0	0	0	0	0	0	0	0	0	5
4	2/1/2023	2/1/2023	0	0	0	0	1	0	0	0	0	0	0	0	1
5	1/2/2023	1/17/2023	5	5	2	0	0	0	0	0	0	0	0	0	12
6	1/2/2023	2/3/2023	5	5	5	5	5	0	0	0	0	0	0	0	25
7	1/2/2023	1/20/2023	5	5	5	0	0	0	0	0	0	0	0	0	15
8	1/2/2023	1/9/2023	5	1	0	0	0	0	0	0	0	0	0	0	6
9	1/9/2023	1/15/2023	0	5	0	0	0	0	0	0	0	0	0	0	5
10	1/3/2023	1/30/2023	4	5	5	5	1	0	0	0	0	0	0	0	20
11	1/18/2023	1/30/2023	0	0	3	5	1	0	0	0	0	0	0	0	9
12	1/21/2023	1/25/2023	0	0	0	3	0	0	0	0	0	0	0	0	3
13	1/16/2023	2/10/2023	0	0	5	5	5	5	0	0	0	0	0	0	20
Sheet7

Cell Formulas
Range	Formula
D1:N1	D1	=C1+7
C3:N13	C3	=MAX(0,NETWORKDAYS.INTL(MAX(C$1,$A3),MIN(C$1+6,$B3),1))
O3:O13	O3	=NETWORKDAYS.INTL(A3,B3,1)

 

[Dave Patton]

N.B. You did not show what version of excel that you are using.

If you have Networkdays.intl use JamesCanale's solution in #2.

T202212a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2		Start	End	Total		02-Jan-2023	09-Jan-2023	16-Jan-2023	23-Jan-2023	30-Jan-2023	06-Feb-2023	13-Feb-2023
3		2-Jan-23	6-Jan-23	5		5
4		2-Jan-23	17-Jan-23	12		5	5	2
5		24-01-23	10-02-23	14					5	5	4
6
2a

Cell Formulas
Range	Formula
G2:L2	G2	=F2+7
F3:L5	F3	=($B3<F$2+6)*MAX(MIN(5,$D3-SUM($E3:E3)),0)
D3:D5	D3	=NETWORKDAYS(B3,C3)

 

[ExcelNovice2017]

Thank you both very much, both ways work a treat, I do have NETWORK.INTL - so I have prompted for that option

I do already have a holiday list as well, at the moment only includes this years bank holidays, I have added the list to define Name and also added this formula as a define name to shorten it and avoid error. As the data entry sheet will be used for the team to hopefully manage their selves and input there own start/end dates

Many thanks

Ian

 

[JamesCanale]

Defining a Name range for holidays is exactly the way to go for that.

 

[ExcelNovice2017]

Defining a Name range for holidays is exactly the way to go for that.

Cheers James - It all came good and glad to say, just starting to us excel again after many years away due to a change of career and I forgotten how much I actually enjoyed using excel but also how frustrating it can also me lol