Counting working Hours in Specific Month between Two Date/times

[Taul]

Hi,
I looking for assistance to modify a formula from Barry Houdini (linked below). The linked formula provides basically what I'm looking for but I would like modify it in two ways:-
1. To show Working hours per month, rather than days.
2. To include Working start time (typically 08:30) and Working End time (typically 17:00), these time may be modified, so I would like them in a separate cell.

Start date & time in cell A2
End date & time in cell B2
Working Start time in cell A7 (08:30)
Working End time in cell B7 (17:00)
Holidays in range Z2:Z10
Formula in C2 and copied across to column N2 to cover the header months 1-Jan-23 to 1-Dec-23 (Rolling months/year in cells C1 to N1)

Is anyone able to assist in modifying the formula please, so far all my attempts have failed.
'=MAX(0,NETWORKDAYS.INTL(MAX(C$1,$A2),MIN(EOMONTH(C$1,0),$B2),"1000011",$Z$2:$Z$10))

You can amend that formula to only show working days based on your definition, so with the same setup, start date in A2, end date in B2 and the 1st of each month in C1 across, try this formula in C2, incorporating Caribeiro77's NETWORKDAYS.INTL suggestion, copied across and down if required

=MAX(0,NETWORKDAYS.INTL(MAX(C$1,$A2),MIN(EOMONTH(C$1,0),$B2),"1000011",$Z$2:$Z$10))

where Z2:Z10 contains your holiday dates, change as required

 

[awoohaw]

what weekday(s) are weekends? Sat/Sun, Fri/Sat, etc?, nevermind, i see it Mon, Sat, Sun.

 

[awoohaw]

Okay, I think I have it figured out for you, try this:

mr excel questions 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O											Z
1	Start	End	2023-01-01	2023-02-01	2023-03-01	2023-04-01	2023-05-01	2023-06-01	2023-07-01	2023-08-01	2023-09-01	2023-10-01	2023-11-01	2023-12-01												Holidays
2	2023-02-12 12:00	2023-03-15 16:00	0	77	71.5	0	0	0	0	0	0	0	0	0												2023-01-01
3																										2023-01-21
4																										2023-02-17
5																										2023-05-26
6	WorkStart	WorkEnd																								2023-07-04
7	08:30	17:00																								2023-09-03
8																										2023-10-12
9																										2023-11-27
10																										2023-12-25
11
Sheet3

Cell Formulas
Range	Formula
C1,Z2	C1	=DATE(2023,1,1)
D1:N1	D1	=EDATE(C1,1)
C2:N2	C2	= (--(($A2>=C$1)*($A2<EDATE(C$1,1))))* IF(INT($A2)=INT($B2),($B2-$A2),IF(MOD($A2,1)>MOD($A7,1),TIME(17,0,0)-MOD($A2,1),$B7-$A7))*24+ (--(($B2>=C$1)*($B2<EDATE(C$1,1))))* IF(INT($A2)=INT($B2),0,MIN(MOD($B2,1),MOD($B7,1))-MOD($A7,1))*24+ ((--($A2>=C$1)*($A2<EDATE(C$1,1)))+(--($B2>=C$1)*($B2<EDATE(C$1,1))))* (NETWORKDAYS.INTL(MAX(C$1,INT($A2+1)),MIN(EDATE(C$1,1),($B2-1)),"1000011",$Z$2:$Z$10)*8)
Z3	Z3	=DATE(2023,1,21)
Z4	Z4	=DATE(2023,2,17)
Z5	Z5	=DATE(2023,5,26)
Z6	Z6	=DATE(2023,7,4)
Z7	Z7	=DATE(2023,9,3)
Z8	Z8	=DATE(2023,10,12)
Z9	Z9	=DATE(2023,11,27)
Z10	Z10	=DATE(2023,12,25)

 

[Taul]

Hi awoohaw,
Thank you so much for looking at this. I have tried your formula but there appears to be an issue if I have the start and end dates on the same day, it results in a negative number.
For example,
Start Date = Monday 24/Jan/2023 08:30
End Date = Monday 24/Jan/2023 17:00
This should result in 8.5 hrs (8:30) but it gives negative 39.5 hrs

I have moved the "work start" row down a little to make more room and changed the days range to only have sat & sun as the weekend ("0000011")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N												Z
1	Start	End	01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023	01/07/2023	01/08/2023	01/09/2023	01/10/2023	01/11/2023	01/12/2023												Holidays
2	Sun 12/02/2023 12:00	Wed 15/03/2023 16:00	0	101	87.5	0	0	0	0	0	0	0	0	0												01/01/2023
3	Thu 20/04/2023 08:00	Thu 20/04/2023 17:00	0	0	0	-39	0	0	0	0	0	0	0	0												21/01/2023
4	Tue 24/01/2023 08:30	Tue 24/01/2023 17:00	-39.5	0	0	0	0	0	0	0	0	0	0	0												17/02/2023
5	Tue 14/02/2023 08:30	Wed 03/05/2023 17:00	0	88.5	0	0	24.5	0	0	0	0	0	0	0												26/05/2023
6																										04/07/2023
7																										03/09/2023
8																										12/10/2023
9	Work Start	Work End	duration (hr)																							27/11/2023
10	08:30	17:00	08:30																							25/12/2023
Sheet1

Cell Formulas
Range	Formula
C1	C1	=DATE(2023,1,1)
D1:N1	D1	=EDATE(C1,1)
C2:N5	C2	= (--(($A2>=C$1)*($A2<EDATE(C$1,1))))* IF(INT($A2)=INT($B2),($B2-$A2),IF(MOD($A2,1)>MOD($A$10,1),TIME(17,0,0)-MOD($A2,1),$B$10-$A$10))*24+ (--(($B2>=C$1)*($B2<EDATE(C$1,1))))* IF(INT($A2)=INT($B2),0,MIN(MOD($B2,1),MOD($B$10,1))-MOD($A$10,1))*24+ ((--($A2>=C$1)*($A2<EDATE(C$1,1)))+(--($B2>=C$1)*($B2<EDATE(C$1,1))))* (NETWORKDAYS.INTL(MAX(C$1,INT($A2+1)),MIN(EDATE(C$1,1),($B2-1)),"0000011",$Z$2:$Z$10)*8)
C10	C10	=B10-A10

 

[awoohaw]

I'm checking on it. that was one of the first things I worked on, but I seem to have subsequently deleted that part.
I also just realized, I multiplied the full days incorrectly. I'l fix that on my end as well.

 

[Taul]

I also found another one of Barry Houdini's formula that provides most of what I'm looking for but it does not distribute the hours over each month.
I think it may need a combination of the original formula (using EOMONTH) and this one.

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Start	End	01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023	01/07/2023	01/08/2023	01/09/2023	01/10/2023	01/11/2023	01/12/2023		Holidays
2	Tue 14/02/2023 08:30	Wed 03/05/2023 17:00	476:00													01/01/2023
3	Tue 14/02/2023 08:30	Tue 14/02/2023 17:00	8:30													21/01/2023
4																17/02/2023
5																26/05/2023
6																04/07/2023
7																03/09/2023
8																12/10/2023
9	Work Start	Work End	duration (hr)													27/11/2023
10	08:30	17:00	08:30													25/12/2023
11	Start1	EndT1	HrsT1
Sheet2

Cell Formulas
Range	Formula
D1:N1	D1	=EDATE(C1,1)
C1	C1	=DATE(2023,1,1)
C2:C3	C2	=IF(A2>B2,0,+HrsT1*(NETWORKDAYS.INTL(A2,B2,"0000011",Holidays)-1)+IF(NETWORKDAYS.INTL(B2,B2,"0000011",Holidays)=0,MAX(Start1,EndT1),MAX(Start1,MIN(MOD(B2,1),EndT1)))-IF(NETWORKDAYS.INTL(A2,A2,"0000011",Holidays)=0,MIN(Start1,EndT1),MIN(EndT1,MAX(MOD(A2,1),Start1))))
C10	C10	=B10-A10

Named Ranges
Name	Refers To	Cells
EndT1	=Sheet2!$B$10	C10, C2:C3
Holidays	=Sheet2!$P$2:$P$10	C2:C3
HrsT1	=Sheet2!$C$10	C2:C3
Start1	=Sheet2!$A$10	C10, C2:C3

 

[awoohaw]

Hey, I found a couple other errors i had also.

Try this:

Nope it had errors, I deleted.

 

[Taul]

Thank you for looking at this. I think post #6 is the closest to capturing all that is needed, just need to figure out how the spread it over each month.
At the moment it captures the total time between the date/times.

 

[awoohaw]

Thank you for looking at this. I think post #6 is the closest to capturing all that is needed, just need to figure out how the spread it over each month.
At the moment it captures the total time between the date/times.

i'm not giving up. I am taking this as a challenge to myself. I almost have it. stay tuned.

 

[awoohaw]

@Taul
Okay, this is the best I have so far. It does have one flaw. If you start on a weekend or holiday it will add one extra day of hours (in this case 8.5). Although, it can be fixed with an edit check and subtracting I can't do that right now.

mr excel questions 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Start	End		2023-01-01	2023-02-01	2023-03-01	2023-04-01	2023-05-01	2023-06-01	2023-07-01	2023-08-01	2023-09-01	2023-10-01	2023-11-01	2023-12-01
2				0	1	0	0	0	0
3	Sat 2023-02-25 08:45	Tue 2023-02-28 13:00		0.00	17.25	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
4	Sun 2023-02-12 12:00	Wed 2023-03-15 16:00		0.00	97.50	92.50	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
5	Thu 2023-04-20 08:00	Thu 2023-04-20 17:15		0.00	0.00	0.00	9.25	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	Wed 2023-02-22 10:30	Thu 2023-05-25 12:00		0.00	35.50	195.50	170.00	156.50	0.00	0.00	0.00	0.00	0.00	0.00	0.00
7	Wed 2023-01-25 08:30	Wed 2023-01-25 16:00		7.50	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
8
9
10	WorkStart	WorkEnd
11	08:30	17:00
12	8.50	17.00
13
Taul

Cell Formulas
Range	Formula
D1	D1	=DATE(2023,1,1)
E1:O1	E1	=EDATE(D1,1)
D2:I2	D2	=((--(D$1<=$B3))*(--($A3<EDATE(D$1,1))))
D3:O7	D3	=((--(D$1<=$B3))*(--($A3<EDATE(D$1,1))))*( IF(MONTH($A3)=MONTH(D$1),MOD($B3,1)-MOD($A3,1),0)*24 + IF(INT($A3)=INT($B3),0,IF(MONTH($B3)=MONTH(D$1),MOD($B3,1)-MOD($A$11,1),0))*24 + MAX(0,NETWORKDAYS.INTL( IF(MONTH($A3)=MONTH(D$1),$A3+1,D$1), INT(IF(MONTH($B3)=MONTH(D$1),$B3,EDATE(D$1,1))-1), 1,$Q$3:$Q$11))* (24*(MOD($B$11,1)-MOD($A$11,1))) )
A12:B12	A12	=MOD(A11,1)*24