Time Difference (Working Minutes)

[duttom]

Hello,

I was wondering if anybody could help, please.

I have a spreadsheet where I am trying the work out the time difference between when a job came in and when it was resolved - but I want the output to only count hours and minutes during the business's operating hours.

I have been able to work out the total time difference, as below (Sheet 1).

But working out the working metrics is what I'm struggling with.

I only want the hours / minutes to be counted if they fall within the working hours which are specified on Sheet 2.

Thanks in advance for any help.

 

[AlanY]

The formulas are based on

How to Calculate Net Work Hours Between Two Dates - Excel Tactics

Please check thoroughly before use.

The net working hours/minutes are on Cols H & I.

I’d added a dummy row 22 to test the holidays exclusion.

Net Work Hours Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Holidays				Start Time	End Time
2	03/01/2022			Mon-Fri	09:00	20:00
3	15/04/2022			Sat	09:00	18:00
4	18/04/2022			Sun	09:00	17:00
5	02/05/2022
6	02/06/2022
7	03/06/2022
8	29/08/2022
9	26/12/2022
10	27/12/2022
11			Start Date	End Date	Net Work Hours (Mon-Fri)	Net Work Hours (Sat)	Net Work Hours (Sun)	Net Work Hours	Net Work Mins
12			09/05/2022 06:50	09/05/2022 13:59	4.98	0.00	0.00	4.98	299
13			31/05/2022 15:10	31/05/2022 17:26	2.27	0.00	0.00	2.27	136
14			11/05/2022 19:32	12/05/2022 10:02	1.50	0.00	0.00	1.50	90
15			03/05/2022 17:34	03/05/2022 18:32	0.97	0.00	0.00	0.97	58
16			25/05/2022 23:36	26/05/2022 10:24	1.40	0.00	0.00	1.40	84
17			05/05/2022 10:48	05/05/2022 11:32	0.73	0.00	0.00	0.73	44
18			06/06/2022 13:06	07/06/2022 15:03	12.95	0.00	0.00	12.95	777
19			28/05/2022 10:02	28/05/2022 12:21	0.00	2.32	0.00	2.32	139
20			29/05/2022 16:58	30/05/2022 09:08	0.13	0.00	0.03	0.17	10
21			11/05/2022 11:32	12/05/2022 09:24	8.87	0.00	0.00	8.87	532
22			01/06/2022 13:06	07/06/2022 15:03	23.95	9.00	8.00	40.95	2457
Net Work Hours

Cell Formulas
Range	Formula
E12:E22	E12	=IF(OR($F$2<$E$2,D12<C12),0,(NETWORKDAYS(C12,D12,HolidayList)-(NETWORKDAYS(C12,C12,HolidayList)*IF(MOD(C12,1)>$F$2,1,(MAX($E$2,MOD(C12,1))-$E$2)/($F$2-$E$2)))-(NETWORKDAYS(D12,D12,HolidayList)*IF(MOD(D12,1)<$E$2,1,($F$2-MIN($F$2,MOD(D12,1)))/($F$2-$E$2))))*($F$2-$E$2)*24)
F12:F22	F12	=(NETWORKDAYS.INTL(C12,D12,"1111101",HolidayList)-(NETWORKDAYS.INTL(C12,C12,"1111101",HolidayList)*IF(MOD(C12,1)>$F$3,1,(MAX($E$3,MOD(C12,1))-$E$3)/($F$3-$E$3)))-(NETWORKDAYS.INTL(D12,D12,"1111101",HolidayList)*IF(MOD(D12,1)<$E$3,1,($F$3-MIN($F$3,MOD(D12,1)))/($F$3-$E$3))))*($F$3-$E$3)*24
G12:G22	G12	=(NETWORKDAYS.INTL(C12,D12,"1111110",HolidayList)-(NETWORKDAYS.INTL(C12,C12,"1111110",HolidayList)*IF(MOD(C12,1)>$F$4,1,(MAX($E$4,MOD(C12,1))-$E$4)/($F$4-$E$4)))-(NETWORKDAYS.INTL(D12,D12,"1111110",HolidayList)*IF(MOD(D12,1)<$E$4,1,($F$4-MIN($F$4,MOD(D12,1)))/($F$4-$E$4))))*($F$4-$E$4)*24
H12:H22	H12	=SUM(E12:G12)
I12:I22	I12	=SUM(E12:G12)*60

Named Ranges
Name	Refers To	Cells
HolidayList	='Net Work Hours'!$A$2:$A$10	E12:G22

 

[duttom]

The formulas are based on

How to Calculate Net Work Hours Between Two Dates - Excel Tactics

Please check thoroughly before use.

The net working hours/minutes are on Cols H & I.

I’d added a dummy row 22 to test the holidays exclusion.

Net Work Hours Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Holidays				Start Time	End Time
2	03/01/2022			Mon-Fri	09:00	20:00
3	15/04/2022			Sat	09:00	18:00
4	18/04/2022			Sun	09:00	17:00
5	02/05/2022
6	02/06/2022
7	03/06/2022
8	29/08/2022
9	26/12/2022
10	27/12/2022
11			Start Date	End Date	Net Work Hours (Mon-Fri)	Net Work Hours (Sat)	Net Work Hours (Sun)	Net Work Hours	Net Work Mins
12			09/05/2022 06:50	09/05/2022 13:59	4.98	0.00	0.00	4.98	299
13			31/05/2022 15:10	31/05/2022 17:26	2.27	0.00	0.00	2.27	136
14			11/05/2022 19:32	12/05/2022 10:02	1.50	0.00	0.00	1.50	90
15			03/05/2022 17:34	03/05/2022 18:32	0.97	0.00	0.00	0.97	58
16			25/05/2022 23:36	26/05/2022 10:24	1.40	0.00	0.00	1.40	84
17			05/05/2022 10:48	05/05/2022 11:32	0.73	0.00	0.00	0.73	44
18			06/06/2022 13:06	07/06/2022 15:03	12.95	0.00	0.00	12.95	777
19			28/05/2022 10:02	28/05/2022 12:21	0.00	2.32	0.00	2.32	139
20			29/05/2022 16:58	30/05/2022 09:08	0.13	0.00	0.03	0.17	10
21			11/05/2022 11:32	12/05/2022 09:24	8.87	0.00	0.00	8.87	532
22			01/06/2022 13:06	07/06/2022 15:03	23.95	9.00	8.00	40.95	2457
Net Work Hours

Cell Formulas
Range	Formula
E12:E22	E12	=IF(OR($F$2<$E$2,D12<C12),0,(NETWORKDAYS(C12,D12,HolidayList)-(NETWORKDAYS(C12,C12,HolidayList)*IF(MOD(C12,1)>$F$2,1,(MAX($E$2,MOD(C12,1))-$E$2)/($F$2-$E$2)))-(NETWORKDAYS(D12,D12,HolidayList)*IF(MOD(D12,1)<$E$2,1,($F$2-MIN($F$2,MOD(D12,1)))/($F$2-$E$2))))*($F$2-$E$2)*24)
F12:F22	F12	=(NETWORKDAYS.INTL(C12,D12,"1111101",HolidayList)-(NETWORKDAYS.INTL(C12,C12,"1111101",HolidayList)*IF(MOD(C12,1)>$F$3,1,(MAX($E$3,MOD(C12,1))-$E$3)/($F$3-$E$3)))-(NETWORKDAYS.INTL(D12,D12,"1111101",HolidayList)*IF(MOD(D12,1)<$E$3,1,($F$3-MIN($F$3,MOD(D12,1)))/($F$3-$E$3))))*($F$3-$E$3)*24
G12:G22	G12	=(NETWORKDAYS.INTL(C12,D12,"1111110",HolidayList)-(NETWORKDAYS.INTL(C12,C12,"1111110",HolidayList)*IF(MOD(C12,1)>$F$4,1,(MAX($E$4,MOD(C12,1))-$E$4)/($F$4-$E$4)))-(NETWORKDAYS.INTL(D12,D12,"1111110",HolidayList)*IF(MOD(D12,1)<$E$4,1,($F$4-MIN($F$4,MOD(D12,1)))/($F$4-$E$4))))*($F$4-$E$4)*24
H12:H22	H12	=SUM(E12:G12)
I12:I22	I12	=SUM(E12:G12)*60

Named Ranges
Name	Refers To	Cells
HolidayList	='Net Work Hours'!$A$2:$A$10	E12:G22

Thank you very much, AlanY! This has been really helpful.

 

[AlanY]

Thank you very much, AlanY! This has been really helpful.

you're welcome