Getting incorrect values for business hours between 2 dates and times

[Suu]

Hey everyone,

I'm trying to calculate the number of business hours between 2 dates + times. I've tried using two formulas and was able to get correct values for entries with the same date, but for lines with different created times and resolved times, I'm getting incorrect values. I could use some help asap.

Business Hours Calculation.xlsx
	E	F	G	H	I	J	K
1	Create Time	Resolved Time				8:00:00 AM	7:00:00 PM
2	8/18/2021 5:09 PM	8/18/2021 5:44 PM	0:35:12	0:35:12		Opening	Closing
3	8/18/2021 5:35 PM	8/19/2021 3:36 PM	-12:59:21	#VALUE!
4	8/23/2021 2:20 PM	8/23/2021 3:59 PM	1:38:07	1:38:07
5	8/24/2021 1:34 PM	8/24/2021 1:49 PM	0:15:00	0:15:00
6	8/19/2021 2:25 PM	8/22/2021 8:58 AM	-16:27:20	-14:25:50
7	9/2/2021 12:26 PM	9/2/2021 2:01 PM	1:34:32	1:34:32
8	8/19/2021 4:03 PM	8/19/2021 4:32 PM	0:28:44	0:28:44
9	8/23/2021 10:50 AM	8/24/2021 7:17 AM	-14:33:06	-3:33:06
10	8/19/2021 11:18 AM	8/19/2021 1:54 PM	2:36:18	2:36:18
11	8/18/2021 9:16 AM	8/18/2021 10:56 AM	1:40:08	1:40:08
12	8/18/2021 11:36 AM	8/18/2021 12:00 PM	0:23:54	0:23:54
13	8/19/2021 4:45 PM	8/19/2021 5:27 PM	0:41:56	0:41:56
14	8/20/2021 11:57 AM	8/22/2021 7:08 AM	-4:49:26	-11:57:43
15	8/19/2021 4:42 PM	8/22/2021 10:28 AM	-17:14:09	-16:42:10
16	8/19/2021 10:37 AM	8/19/2021 4:11 PM	5:33:42	5:33:42
17	8/19/2021 4:42 PM	8/19/2021 5:10 PM	0:28:14	0:28:14
18	8/19/2021 1:23 PM	8/19/2021 4:57 PM	3:33:18	3:33:18
19	8/22/2021 12:19 PM	8/23/2021 8:56 AM	-3:23:04	8:56:40
20	8/24/2021 10:23 AM	8/25/2021 6:39 AM	-14:43:55	-3:43:55
21	8/30/2021 8:29 AM	8/30/2021 8:50 AM	0:20:27	0:20:27
22	10/5/2021 12:52 PM	10/5/2021 1:43 PM	0:50:49	0:50:49
23	8/27/2021 10:11 AM	8/29/2021 9:49 AM	-0:22:10	-10:11:13
24	8/19/2021 2:41 PM	8/19/2021 4:31 PM	1:49:14	1:49:14
25	9/6/2021 10:50 AM	9/6/2021 2:07 PM	3:17:04	3:17:04
26	9/6/2021 5:06 PM	9/6/2021 6:01 PM	0:55:43	0:55:43
Sheet1

Cell Formulas
Range	Formula
G2:G26	G2	=(NETWORKDAYS(E2,F2)-1)*($J$1-$K$1)+MOD(F2,1)-MOD(E2,1)
H2:H26	H2	=(NETWORKDAYS(E2,F2)-1)*(J1-K1) +IF(NETWORKDAYS(F2,F2),MEDIAN(MOD(F2,1),J1,K1),J1) -MEDIAN(NETWORKDAYS(E2,E2)*MOD(E2,1),J1,K1)

 

[jsb1921]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Create Time	Resolved Time	Days	part0	part1	part2	part3	Tot	1/0/00 8:00 AM	1/0/00 7:00 PM	11:00:00
2					8/17/17 5:09 PM	8/17/17 5:44 PM	0	0:35:12	0	0	0	0:35:12	Opening	Closing	BusHrs
3					8/17/17 5:35 PM	8/18/17 3:36 PM	1	0	1:24:36	0:00:00	7:36:03	9:00:39
4					8/22/17 2:20 PM	8/22/17 3:59 PM	0	1:38:07	0	0	0	1:38:07
5					8/23/17 1:34 PM	8/23/17 1:49 PM	0	0:15:00	0	0	0	0:15:00
6					8/18/17 2:25 PM	8/21/17 8:58 AM	1	0	4:34:10	0:00:00	0:58:30	5:32:40
7					9/1/17 12:26 PM	9/1/17 2:01 PM	0	1:34:32	0	0	0	1:34:32
8					8/18/17 4:03 PM	8/18/17 4:32 PM	0	0:28:44	0	0	0	0:28:44
9					8/22/17 10:50 AM	8/23/17 7:17 AM	1	0	8:09:02	0:00:00	0	8:09:02
10					8/18/17 11:18 AM	8/18/17 1:54 PM	0	2:36:18	0	0	0	2:36:18
11					8/17/17 9:16 AM	8/17/17 10:56 AM	0	1:40:08	0	0	0	1:40:08
12					8/17/17 11:36 AM	8/17/17 12:00 PM	0	0:23:54	0	0	0	0:23:54
13					8/18/17 4:45 PM	8/18/17 5:27 PM	0	0:41:56	0	0	0	0:41:56
14					8/19/17 11:57 AM	8/21/17 7:08 AM	0	19:10:34	0	0	0	19:10:34
15					8/18/17 4:42 PM	8/21/17 10:28 AM	1	0	2:17:50	0:00:00	2:28:01	4:45:51
16					8/18/17 10:37 AM	8/18/17 4:11 PM	0	5:33:42	0	0	0	5:33:42
17					8/18/17 4:42 PM	8/18/17 5:10 PM	0	0:28:14	0	0	0	0:28:14
18					8/18/17 1:23 PM	8/18/17 4:57 PM	0	3:33:18	0	0	0	3:33:18
19					8/21/17 12:19 PM	8/22/17 8:56 AM	1	0	6:40:16	0:00:00	0:56:40	7:36:56
20					8/23/17 10:23 AM	8/24/17 6:39 AM	1	0	8:36:12	0:00:00	0	8:36:12
21					8/29/17 8:29 AM	8/29/17 8:50 AM	0	0:20:27	0	0	0	0:20:27
22					10/4/17 12:52 PM	10/4/17 1:43 PM	0	0:50:49	0	0	0	0:50:49
23					8/26/17 10:11 AM	8/28/17 9:49 AM	0	23:37:50	0	0	0	23:37:50
24					8/18/17 2:41 PM	8/18/17 4:31 PM	0	1:49:14	0	0	0	1:49:14
25					9/5/17 10:50 AM	9/5/17 2:07 PM	0	3:17:04	0	0	0	3:17:04
26					9/5/17 5:06 PM	9/5/17 6:01 PM	0	0:55:43	0	0	0	0:55:43
27										0		0:00:00
28					9/5/17 11:06 PM	9/6/17 9:01 PM	1	0	0	0:00:00	13:01:48	13:01:48
29					9/5/17 8:06 AM	9/7/17 9:00 AM	2	0	10:54:00	11:00:00	1:00:00	22:54:00
30
Sheet8

Cell Formulas
Range	Formula
O1	O1	=TEXT($N$1-$M$1,"h:mm:ss")
G28:G29,G2:G26	G2	=INT(NETWORKDAYS(E2,F2)-1)
H28:H29,H2:H26	H2	=IF($G2=0,TEXT(F2-E2,"h:mm:ss"),0)
I28:I29,I2:I26	I2	=IF($G2>0,IF(HOUR($E2)>HOUR($N$1),0,TEXT($N$1-TIME(HOUR($E2),MINUTE($E2),SECOND($E2)),"h:mm:ss")),0)
K28:K29,K2:K26	K2	=IF($G2>0,IF(HOUR($F2)<HOUR($M$1),0,TEXT((TIME(HOUR($F2),MINUTE($F2),SECOND($F2))-$M$1),"h:mm:ss")),0)
J2:J29	J2	=IF($G2>0,TEXT(($G2-1)*$O$1,"h:mm:ss"),0)
L2:L29	L2	=TEXT($H2+$I2+J2+$K2,"h:mm:ss")

I have intentionally split the work using lot of intermediate columns so that it will be easy to understand the concept.
Split the work in to 6 steps.
1: calculate number of days between the date created and date resolved. it is in col G
2. Part 0 will only handle the cases when it is created and resolved in the same day. it is in col H. If dt created and dt resolved are same dt, part2, part3, part4 will be always 0.
3. Part2, part3, part4 will deal when dt created and dt resolved are different.
a) part2 is the hours spent between hour created and endofthe day of business.
b) part3 is 11 hrs add for every day the ticket remains not closed.
c) part4 is the hours spent on the last day from businessopening to time resolved.
if you add part0, part1, part2, part3 that will give you tot hours ticket remains opened.

you have to decide about error/overtime. what happens if the ticket created is after businessisclosed for the day. do you give them overtime.
what to do if the ticket is closed at 5am, even though business only opens at 7am.
what to do if ticket resolved time is same date but earlier.
etc.
though it can be done in formula like this, it is a good case to write a vba macro function and you can simply use it like any other excel functions.
If it helps, pl mark it as a solution.