Getting incorrect values for business hours between 2 dates and times

Suu

New Member
Joined
Mar 28, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
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
EFGHIJK
1Create TimeResolved Time8:00:00 AM7:00:00 PM
28/18/2021 5:09 PM8/18/2021 5:44 PM0:35:120:35:12OpeningClosing
38/18/2021 5:35 PM8/19/2021 3:36 PM-12:59:21#VALUE!
48/23/2021 2:20 PM8/23/2021 3:59 PM1:38:071:38:07
58/24/2021 1:34 PM8/24/2021 1:49 PM0:15:000:15:00
68/19/2021 2:25 PM8/22/2021 8:58 AM-16:27:20-14:25:50
79/2/2021 12:26 PM9/2/2021 2:01 PM1:34:321:34:32
88/19/2021 4:03 PM8/19/2021 4:32 PM0:28:440:28:44
98/23/2021 10:50 AM8/24/2021 7:17 AM-14:33:06-3:33:06
108/19/2021 11:18 AM8/19/2021 1:54 PM2:36:182:36:18
118/18/2021 9:16 AM8/18/2021 10:56 AM1:40:081:40:08
128/18/2021 11:36 AM8/18/2021 12:00 PM0:23:540:23:54
138/19/2021 4:45 PM8/19/2021 5:27 PM0:41:560:41:56
148/20/2021 11:57 AM8/22/2021 7:08 AM-4:49:26-11:57:43
158/19/2021 4:42 PM8/22/2021 10:28 AM-17:14:09-16:42:10
168/19/2021 10:37 AM8/19/2021 4:11 PM5:33:425:33:42
178/19/2021 4:42 PM8/19/2021 5:10 PM0:28:140:28:14
188/19/2021 1:23 PM8/19/2021 4:57 PM3:33:183:33:18
198/22/2021 12:19 PM8/23/2021 8:56 AM-3:23:048:56:40
208/24/2021 10:23 AM8/25/2021 6:39 AM-14:43:55-3:43:55
218/30/2021 8:29 AM8/30/2021 8:50 AM0:20:270:20:27
2210/5/2021 12:52 PM10/5/2021 1:43 PM0:50:490:50:49
238/27/2021 10:11 AM8/29/2021 9:49 AM-0:22:10-10:11:13
248/19/2021 2:41 PM8/19/2021 4:31 PM1:49:141:49:14
259/6/2021 10:50 AM9/6/2021 2:07 PM3:17:043:17:04
269/6/2021 5:06 PM9/6/2021 6:01 PM0:55:430:55:43
Sheet1
Cell Formulas
RangeFormula
G2:G26G2=(NETWORKDAYS(E2,F2)-1)*($J$1-$K$1)+MOD(F2,1)-MOD(E2,1)
H2:H26H2=(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)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
Book1
ABCDEFGHIJKLMNO
1Create TimeResolved TimeDayspart0part1part2part3Tot1/0/00 8:00 AM1/0/00 7:00 PM11:00:00
28/17/17 5:09 PM8/17/17 5:44 PM00:35:120000:35:12OpeningClosingBusHrs
38/17/17 5:35 PM8/18/17 3:36 PM101:24:360:00:007:36:039:00:39
48/22/17 2:20 PM8/22/17 3:59 PM01:38:070001:38:07
58/23/17 1:34 PM8/23/17 1:49 PM00:15:000000:15:00
68/18/17 2:25 PM8/21/17 8:58 AM104:34:100:00:000:58:305:32:40
79/1/17 12:26 PM9/1/17 2:01 PM01:34:320001:34:32
88/18/17 4:03 PM8/18/17 4:32 PM00:28:440000:28:44
98/22/17 10:50 AM8/23/17 7:17 AM108:09:020:00:0008:09:02
108/18/17 11:18 AM8/18/17 1:54 PM02:36:180002:36:18
118/17/17 9:16 AM8/17/17 10:56 AM01:40:080001:40:08
128/17/17 11:36 AM8/17/17 12:00 PM00:23:540000:23:54
138/18/17 4:45 PM8/18/17 5:27 PM00:41:560000:41:56
148/19/17 11:57 AM8/21/17 7:08 AM019:10:3400019:10:34
158/18/17 4:42 PM8/21/17 10:28 AM102:17:500:00:002:28:014:45:51
168/18/17 10:37 AM8/18/17 4:11 PM05:33:420005:33:42
178/18/17 4:42 PM8/18/17 5:10 PM00:28:140000:28:14
188/18/17 1:23 PM8/18/17 4:57 PM03:33:180003:33:18
198/21/17 12:19 PM8/22/17 8:56 AM106:40:160:00:000:56:407:36:56
208/23/17 10:23 AM8/24/17 6:39 AM108:36:120:00:0008:36:12
218/29/17 8:29 AM8/29/17 8:50 AM00:20:270000:20:27
2210/4/17 12:52 PM10/4/17 1:43 PM00:50:490000:50:49
238/26/17 10:11 AM8/28/17 9:49 AM023:37:5000023:37:50
248/18/17 2:41 PM8/18/17 4:31 PM01:49:140001:49:14
259/5/17 10:50 AM9/5/17 2:07 PM03:17:040003:17:04
269/5/17 5:06 PM9/5/17 6:01 PM00:55:430000:55:43
2700:00:00
289/5/17 11:06 PM9/6/17 9:01 PM1000:00:0013:01:4813:01:48
299/5/17 8:06 AM9/7/17 9:00 AM2010:54:0011:00:001:00:0022:54:00
30
Sheet8
Cell Formulas
RangeFormula
O1O1=TEXT($N$1-$M$1,"h:mm:ss")
G28:G29,G2:G26G2=INT(NETWORKDAYS(E2,F2)-1)
H28:H29,H2:H26H2=IF($G2=0,TEXT(F2-E2,"h:mm:ss"),0)
I28:I29,I2:I26I2=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:K26K2=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:J29J2=IF($G2>0,TEXT(($G2-1)*$O$1,"h:mm:ss"),0)
L2:L29L2=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,106
Messages
5,640,132
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top