Network days in date range

[mrsr1]

Hi,

I have a set of data that has start date 01/01/2018 and end date 30/03/2018, this a total of 65 workings days. How do I split the number of working days into months if the months date range is;

Month	Start date	End Date
Jan	01/01/2018	18/01/2018
Feb	19/01/2018	16/02/2018
Mar	19/02/2018	16/03/2018
Apr	19/03/2018	18/04/2018

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Can I see how many working days are in each month for the 65 days? in the end I would like to have Jan 14, feb 21, mar 20, apr 10
Hope that makes sense.

thanks

 

[James006]

Hi,

Have you tested =NETWORKDAYS(B2,C2)

Hope this will help

 

[mrsr1]

Hi,

Sorry I don't think I am explaining myself very well; I have a data set that looks like this, so I know the network days total but I want to split them by month:

Absence Start	Absence Close	TOTAL
08-Jan-18	12-Jan-18	5.00
22-Oct-18	23-Oct-18	2.00
27-Aug-18	29-Aug-18	3.00
06-Nov-18	09-Nov-18	4.00
04-Jan-18	08-Jan-18	3.00
18-Jun-18	18-Jun-18	1.00
29-Jan-18	02-Feb-18	5.00
29-Jan-18	02-Feb-18	5.00
15-Nov-18	18-Nov-18	2.00
05-Jun-18	10-Jun-18	4.00
30-Jul-18	03-Aug-18	5.00
09-Jan-18	15-Jan-18	5.00
19-Mar-18	23-Mar-18	5.00

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

and I want to know how many working days there are for each month if the month range is:

Month	Start date	End Date
Jan	03/01/2018	18/01/2018
Feb	19/01/2018	16/02/2018
Mar	19/02/2018	16/03/2018
Apr	19/03/2018	18/04/2018
May	19/04/2018	18/05/2018
Jun	21/05/2018	18/06/2018
Jul	19/06/2018	18/07/2018
Aug	19/07/2018	17/08/2018
Sep	20/08/2018	18/09/2018
Oct	19/09/2018	18/10/2018
Nov	19/10/2018	16/11/2018
Dec	19/11/2018	31/12/2018

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

sorry excel is not my friend

 

[Tetra201]

... I have a set of data that has start date 01/01/2018 and end date 30/03/2018, this a total of 65 workings days. How do I split the number of working days into months...

With your month date range data in A1:C5 and the 01/01/2018 and 30/03/2018 in cells F2, G2, try placing the following formula in cell D2 and drag-copying it down:

=MAX(0,NETWORKDAYS(MAX(B2,$F$2),MIN(C2,$G$2)))