Excel - Identify number of business days for a specific month within a weekly date range

[Mgigarcia]

Hi Everyone,

Looking for some expert assistance from a specific use case.

I have a set of columns starting on Monday and a number under each one of those. I have a separate excel divided in columns per month.

16/10/2023: 25 hours (5 business days in October)
23/10/2023: 30 hours (5 business days in October)
30/10/2023: 40 hours (2 business days in October and 3 business days in November)
06/11/2023: 27 hours (5 business days in November)

I need to allocate the numbers of hours belonging to October and November from this list based on the business days (Monday to Friday) for the specific month.

According to the sample above my result should be:

October = 71. 25h first row, 30h second row and 16h third row where there are two months listed (40/5 * 2 business days and for October, 30th and 31st October)
November = 51. 24h third row where there are two months listed (40/5 * 3 business days for November, 1st, 2nd and 3rd November) and 27h fourth row where the whole week belong to November.

I am not managing to do the split in the week where there are days for two different months. Could someone help? Appreciated.

 

[duggie33]

Please provide some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. As of October 2023, the latest version is 2.1. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[Mgigarcia]

Hi, I am unable to install the Add-in for security reasons. I am putting a sample of the table here with the manual formula I use to divide the days between different months. In the sample, the problematic column is Column E that contains 3 business days for October and 2 business days for November. Thank you very much.

	A	B	C	D	E	F	G			Oct	November
1										81​	59​
2										=B4+C4+D4+(E4/5*3)	=(E4/5*2)+F4+G4
3	Date	08/10/2023​	15/10/2023​	22/10/2023​	29/10/2023​	06/11/2023​	13/11/2023​
4	Hours	25​	21​	17​	30​	35​	12​

 

[duggie33]

I had to rearrange your data so my brain could process it. There is likely other ways to achieve the results, my steps led me to this...

Book_2023-10-30.xlsx
	A	B	C	D	E	F	G	H
1				October	November	December	January	February
2	Start Date	End Date	Hours	10	11	12	1	2
3	08/10/2023	14/10/2023	25	25	0	0	0	0
4	15/10/2023	21/10/2023	21	21	0	0	0	0
5	22/10/2023	28/10/2023	17	17	0	0	0	0
6	29/10/2023	04/11/2023	30	12	18	0	0	0
7	05/11/2023	11/11/2023	35	0	35	0	0	0
8	12/11/2023	18/11/2023	12	0	12	0	0	0
9	19/11/2023	25/11/2023	36	0	36	0	0	0
10	26/11/2023	02/12/2023	23	0	18.4	4.6	0	0
11	03/12/2023	09/12/2023	21	0	0	21	0	0
12	10/12/2023	16/12/2023	16	0	0	16	0	0
13	17/12/2023	23/12/2023	29	0	0	29	0	0
14	24/12/2023	30/12/2023	18	0	0	18	0	0
15	31/12/2023	06/01/2024	22	0	0	0	22	0
16	07/01/2024	13/01/2024	35	0	0	0	35	0
17	14/01/2024	20/01/2024	30	0	0	0	30	0
18	21/01/2024	27/01/2024	40	0	0	0	40	0
19	28/01/2024	03/02/2024	18	0	0	0	10.8	7.2
20	04/02/2024	10/02/2024	38	0	0	0	0	38
Sheet6

Cell Formulas
Range	Formula
B3:B20	B3	=A3+6
D3:H20	D3	=IFS(AND(MONTH($A3)=MONTH($B3),MONTH($A3)=D$2),$C3, MONTH($A3)=D$2,NETWORKDAYS($A3,DATE(YEAR($A3),MONTH($A3)+1,1)-1)*$C3/5, MONTH($B3)=D$2,$C3-C3,1,0)
A4:A20	A4	=A3+7

I hope this helps,

Doug