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



## ExcelNovice2017 (Jan 5, 2023)

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 (Jan 5, 2023)

You could also through in a list of holidays somewhere.

MrExcelPlayground15.xlsxABCDEFGHIJKLMNO11/2/20231/9/20231/16/20231/23/20231/30/20232/6/20232/13/20232/20/20232/27/20233/6/20233/13/20233/20/20232StartEnd123456789101112Duration31/2/20231/6/2023500000000000542/1/20232/1/2023000010000000151/2/20231/17/20235520000000001261/2/20232/3/20235555500000002571/2/20231/20/20235550000000001581/2/20231/9/2023510000000000691/9/20231/15/20230500000000005101/3/20231/30/202345551000000020111/18/20231/30/20230035100000009121/21/20231/25/20230003000000003131/16/20232/10/202300555500000020Sheet7Cell FormulasRangeFormulaD1:N1D1=C1+7C3:N13C3=MAX(0,NETWORKDAYS.INTL(MAX(C$1,$A3),MIN(C$1+6,$B3),1))O3:O13O3=NETWORKDAYS.INTL(A3,B3,1)


----------



## Dave Patton (Jan 5, 2023)

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.xlsmABCDEFGHIJKL2StartEndTotal02-Jan-202309-Jan-202316-Jan-202323-Jan-202330-Jan-202306-Feb-202313-Feb-202332-Jan-236-Jan-2355      42-Jan-2317-Jan-2312552    524-01-2310-02-2314   554 62aCell FormulasRangeFormulaG2:L2G2=F2+7F3:L5F3=($B3<F$2+6)*MAX(MIN(5,$D3-SUM($E3:E3)),0)D3:D5D3=NETWORKDAYS(B3,C3)


----------



## ExcelNovice2017 (Jan 5, 2023)

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 (Jan 5, 2023)

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


----------



## ExcelNovice2017 (Jan 6, 2023)

JamesCanale said:


> 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


----------

