Calculate Response time using different working hours during the week and excluding weekends / bankholidays

bannianke

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hello

I need a formula where it calculates Response Time considering business hours Mon - Thu 9am till 9pm and Fri 9am-5pm. In addition Bankholidays and Weekends should be excluded.
Ticket Received Date is shown in Column B , Call Date is shown in Column C.
Formula for Response Time for column D should therefore show time difference in hours considering different Business Hours Mon - Thu (9-9) and on Fri from 9-5.
Staff is not working at weekends and therefore working days only Mon-Fri.
Thanks a lot for your help


1598383956109.png
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,493
Welcome to MrExcel.

Consider:

Book1
ABCDEFGHI
1ReceivedCalledResponse TimeHelper1Helper2Holidays
28/22/2020 20:448/24/2020 11:0502:058/24/2020 9:008/24/2020 11:059/7/2020
38/23/2020 14:048/24/2020 18:3509:358/24/2020 9:008/24/2020 18:3511/26/2020
48/21/2020 17:168/25/2020 9:4512:298/21/2020 17:168/25/2020 9:4512/25/2020
59/3/2020 14:419/8/2020 12:2117:409/3/2020 14:419/8/2020 12:21
68/23/2020 11:118/29/2020 17:1708:008/24/2020 9:008/28/2020 17:00
Sheet11
Cell Formulas
RangeFormula
F2:F6F2=IF(OR(WEEKDAY(B2,2)>5,ISNUMBER(MATCH(INT(B2),$I$2:$I$4,0))),WORKDAY(INT(B2),1,$I$2:$I$4)+9/24,B2)
G2:G6G2=IF(OR(WEEKDAY(C2,2)>5,ISNUMBER(MATCH(INT(C2),$I$2:$I$4,0))),WORKDAY(INT(C2),-1,$I$2:$I$4)+21/24-4/24*(WEEKDAY(WORKDAY(INT(C2),-1,$I$2:$I$4))=6),C2)
D2:D6D2=(SUM(IF(ISERROR(MATCH(ROW(INDIRECT(INT(F2)&":"&INT(G2))),$I$2:$I$4,0)),CHOOSE(WEEKDAY(ROW(INDIRECT(INT(F2)&":"&INT(G2)))),0,12,12,12,12,8,0)))-(24*MOD(F2,1)-9)-(IF(WEEKDAY(G2)=6,17,21)-24*MOD(G2,1)))/24
Press CTRL+SHIFT+ENTER to enter array formulas.


I started with the assumption that any times would be within working hours. However, some of your example times were on weekends. To handle that, I had to add 2 helper columns. If the received or called times were out of business hours, then the F and G formulas would find the closest time that is in the working hours range. Now we can apply the D2 formula which generates the number of hours:minutes between those two times, excluding weekends, holidays, and non-working hours. Note that I used a custom format of hh:mm on column D.
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
139
Office Version
  1. 2016
Platform
  1. Windows
I suggest to use a user-defined function:
MrExcel_Bannianke_sbTimeDiff.xlsm
ABCDEFGHIJ
1ReceivedCalledResponse TimeWorking HoursStartEndUS Holidays
2Sa 22.08.2020 20:44Mo 24.08.2020 11:052:05Monday9:0021:00Mi 01-Jan-2020
3So 23.08.2020 14:04Mo 24.08.2020 18:359:35Tuesday9:0021:00Mo 20-Jan-2020
4Fr 21.08.2020 17:16Di 25.08.2020 09:4512:45Mon 12h + Tue 45 mins = 12:45Wednesday9:0021:00Mo 17-Feb-2020
5Do 03.09.2020 14:41Di 08.09.2020 12:2117:40Thursday9:0021:00Mo 25-Mai-2020
6So 23.08.2020 11:11Sa 29.08.2020 17:1756:00A full week w/o holidays = 56hFriday9:0017:00Fr 03-Jul-2020
7SaturdayMo 07-Sep-2020
8SundayMo 12-Okt-2020
9HolidaysMi 11-Nov-2020
bannianke
Cell Formulas
RangeFormula
C2:C6C2=sbTimeDiff(A2,B2,Work_Hours,US_Holidays)
Named Ranges
NameRefers ToCells
US_Holidays=bannianke!$J$2:$J$22C2:C6
Work_Hours=bannianke!$G$2:$H$9C2:C6


Please note that my results differ from those Eric offered (row 4 and row 6).

A sample file I have stored here:
(Please download, open and use at your own risk - but I am using an up-to-date virus scanning program)

The related article which also presents the UDF I used:
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,493
Upon looking at Sulprobil's example, I realize that my Helper1 and Helper2 formulas are incomplete. The F4 value is not right for example, causing D4 to be off. These can be remedied, but before I work on it, please let us know if either of these approaches works for you, and if they give the results you need.
 

bannianke

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi Eric, Hi Sulprobil

great info and thanks a lot for the speedy assistance , really appreciate it !!!

I tried both options

Eric's approach :
as you mentioned , there is something not quite right with the helper 1 ... see screenshot belw
I marked in red, where something does not bring back the expected result
However it works partially
Not sure if "Helper 2" is needed, as Call Date/Time would always be within office hours / during the week when staff is working ...so we can just take the original Call date/time

1598476163181.png


Formula entered in column D
=(SUM(IF(ISERROR(MATCH(ROW(INDIRECT(INT(F2)&":"&INT(G2))),$I$2:$I$4,0)),CHOOSE(WEEKDAY(ROW(INDIRECT(INT(F2)&":"&INT(G2)))),0,12,12,12,12,8,0)))-(24*MOD(F2,1)-9)-(IF(WEEKDAY(G2)=6,17,21)-24*MOD(G2,1)))/24



Sulprobil's approach:
I like the simplicity off it , but I can't make it work.
Excel does not recognize this "sbtimediff" formula and gives me a "#name?" error message
Below my attempt to copy across the suggested formula
I listed the named range data in a separate work sheet, but should not make a difference

1598476379282.png



1598476437841.png



1598476573540.png



1598476670443.png


Thanks again !!!!!!!!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,493
Try this:

Book1
ABCDEFGHI
1ReceivedCalledResponse TimeHelper1Holidays
28/22/2020 7:448/24/2020 11:052.088/24/2020 9:009/7/2020
38/23/2020 14:448/24/2020 18:359.588/24/2020 9:0011/26/2020
48/21/2020 10:448/25/2020 9:4519.028/21/2020 10:4412/25/2020
58/22/2020 10:448/26/2020 9:4524.758/24/2020 9:00
68/23/2020 10:448/27/2020 9:4536.758/24/2020 9:00
78/24/2020 10:448/28/2020 9:4547.028/24/2020 10:44
88/25/2020 10:448/26/2020 9:4511.028/25/2020 10:44
98/26/2020 10:448/26/2020 11:451.028/26/2020 10:44
107/27/2020 21:448/26/2020 9:45236.757/28/2020 9:00
118/7/2020 19:177/10/2020 9:45Error8/10/2020 9:00
128/7/2020 10:088/7/2020 13:453.628/7/2020 10:08
138/7/2020 5:448/7/2020 9:450.758/7/2020 9:00
148/7/2020 18:218/10/2020 11:132.228/10/2020 9:00
159/4/2020 16:459/8/2020 10:001.259/4/2020 16:45
Sheet1
Cell Formulas
RangeFormula
D2:D15D2=IF(C2<B2,"Error",SUM(IF(ISERROR(MATCH(ROW(INDIRECT(INT(F2)&":"&INT(C2))),$I$2:$I$4,0)),CHOOSE(WEEKDAY(ROW(INDIRECT(INT(F2)&":"&INT(C2)))),0,12,12,12,12,8,0)))-(24*MOD(F2,1)-9)-(IF(WEEKDAY(C2)=6,17,21)-24*MOD(C2,1)))
F2:F15F2=IF(OR(WEEKDAY(B2,2)>5,ISNUMBER(MATCH(INT(B2),$I$2:$I$4,0)),MOD(B2,1)<9/24,MOD(B2,1)>IF(WEEKDAY(B2)=6,17,20)/24),WORKDAY(INT(B2)-IF(MOD(B2,1)<9/24,1,0),1,$I$2:$I$4)+9/24,B2)
Press CTRL+SHIFT+ENTER to enter array formulas.


I believe I have the Helper1 formula right this time. And if the called time will always be in office hours, we won't need the helper2 formula. (Good thing, it would be a pain!) I made 1 little tweak to the response time formula, so that if the response time is earlier than the received time, it says Error instead of calculating an incorrect value. Also, I switched the result to hours and fractions of hours. The hh:mm format failed when the number of hours is over 24. After playing around with other options, I just went to what's there. I checked every example on this sheet, they all look ok.


As far as Sulprobil's approach, did you install the user-defined function? He built a special function to do this, and put it in the worksheet on dropbox. I'll let Sulprobil explain more, but more than likely you're getting the #Name! error because it wasn't installed properly.

Anyway, see if this works for you!
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
139
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

If you download the file at the end of my article you will find your example in there.
Please try to learn about using UDFs (pressing ALT + F11, inserting a new module, copying code in there, storing the file as xls, xlsm, or xlsb).
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,493
Sulprobil kindly pointed out a problem with my latest Helper1 formula, it should be:

Book1
ABCDEFGHI
1ReceivedCalledResponse TimeHelper1Holidays
28/22/20 7:448/24/20 11:052.088/24/20 9:009/7/2020
38/23/20 14:448/24/20 18:359.588/24/20 9:0011/26/2020
48/21/20 10:448/25/20 9:4519.028/21/20 10:4412/25/2020
58/22/20 10:448/26/20 9:4524.758/24/20 9:00
68/23/20 10:448/27/20 9:4536.758/24/20 9:00
78/24/20 10:448/28/20 9:4547.028/24/20 10:44
88/25/20 10:448/26/20 9:4511.028/25/20 10:44
98/26/20 10:448/26/20 11:451.028/26/20 10:44
107/27/20 21:448/26/20 9:45236.757/28/20 9:00
118/7/20 19:177/10/20 9:45Error8/10/20 9:00
128/7/20 10:088/7/20 13:453.628/7/20 10:08
138/7/20 5:448/7/20 9:450.758/7/20 9:00
148/7/20 18:218/10/20 11:132.228/10/20 9:00
159/4/20 16:459/8/20 10:001.259/4/20 16:45
168/25/20 20:048/26/20 20:0011.938/25/20 20:04
Sheet14
Cell Formulas
RangeFormula
D2:D16D2=IF(C2<B2,"Error",SUM(IF(ISERROR(MATCH(ROW(INDIRECT(INT(F2)&":"&INT(C2))),$I$2:$I$4,0)),CHOOSE(WEEKDAY(ROW(INDIRECT(INT(F2)&":"&INT(C2)))),0,12,12,12,12,8,0)))-(24*MOD(F2,1)-9)-(IF(WEEKDAY(C2)=6,17,21)-24*MOD(C2,1)))
F2:F16F2=IF(OR(WEEKDAY(B2,2)>5,ISNUMBER(MATCH(INT(B2),$I$2:$I$4,0)),MOD(B2,1)<9/24,MOD(B2,1)>IF(WEEKDAY(B2)=6,17,21)/24),WORKDAY(INT(B2)-IF(MOD(B2,1)<9/24,1,0),1,$I$2:$I$4)+9/24,B2)
Press CTRL+SHIFT+ENTER to enter array formulas.


It's still pretty complicated, and could have other issues. Test it well.
 

bannianke

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
IT WORKS ...YEAH !!! Brilliant !
Used Sulprobil UDF approach as I still had some mismatches with Eric's version.
Thanks a lot to both of you in assisting me in this matter.
It is a big time saver for me and great help ! Learned also something new as I never used UDFs before :)
 

khaledabdelazim

New Member
Joined
Dec 8, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi All,

I have tested the following formula and it satisfies this need very well, yet I don't know how to adapt it to Friday as a day off instead of the common Sat,Sun

=IF(OR(C4="",D4=""),"",(NETWORKDAYS(C4,D4,$H$4:$H$12)-1)*($G$5-$G$4)+IF(OR(ISNUMBER(MATCH(INT(D4),$H$4:$H$12,0)),WEEKDAY(D4,2)>5),$G$5,MEDIAN(MOD(D4,1),$G$5,$G$4))-IF(OR(ISNUMBER(MATCH(INT(C4),$H$4:$H$12,0)),WEEKDAY(C4,2)>5),$G$4,MEDIAN(MOD(C4,1),$G$5,$G$4)))


I wish someone could help here
 

Watch MrExcel Video

Forum statistics

Threads
1,119,106
Messages
5,576,144
Members
412,700
Latest member
IIII
Top