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

[bannianke]

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

 

[Eric W]

Welcome to MrExcel.

Consider:

Book1
	A	B	C	D	E	F	G	H	I
1		Received	Called	Response Time		Helper1	Helper2		Holidays
2		8/22/2020 20:44	8/24/2020 11:05	02:05		8/24/2020 9:00	8/24/2020 11:05		9/7/2020
3		8/23/2020 14:04	8/24/2020 18:35	09:35		8/24/2020 9:00	8/24/2020 18:35		11/26/2020
4		8/21/2020 17:16	8/25/2020 9:45	12:29		8/21/2020 17:16	8/25/2020 9:45		12/25/2020
5		9/3/2020 14:41	9/8/2020 12:21	17:40		9/3/2020 14:41	9/8/2020 12:21
6		8/23/2020 11:11	8/29/2020 17:17	08:00		8/24/2020 9:00	8/28/2020 17:00
Sheet11

Cell Formulas
Range	Formula
F2:F6	F2	=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:G6	G2	=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:D6	D2	=(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]

I suggest to use a user-defined function:

MrExcel_Bannianke_sbTimeDiff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Received	Called	Response Time			Working Hours	Start	End		US Holidays
2	Sa 22.08.2020 20:44	Mo 24.08.2020 11:05	2:05			Monday	9:00	21:00		Mi 01-Jan-2020
3	So 23.08.2020 14:04	Mo 24.08.2020 18:35	9:35			Tuesday	9:00	21:00		Mo 20-Jan-2020
4	Fr 21.08.2020 17:16	Di 25.08.2020 09:45	12:45	Mon 12h + Tue 45 mins = 12:45		Wednesday	9:00	21:00		Mo 17-Feb-2020
5	Do 03.09.2020 14:41	Di 08.09.2020 12:21	17:40			Thursday	9:00	21:00		Mo 25-Mai-2020
6	So 23.08.2020 11:11	Sa 29.08.2020 17:17	56:00	A full week w/o holidays = 56h		Friday	9:00	17:00		Fr 03-Jul-2020
7						Saturday				Mo 07-Sep-2020
8						Sunday				Mo 12-Okt-2020
9						Holidays				Mi 11-Nov-2020
bannianke

Cell Formulas
Range	Formula
C2:C6	C2	=sbTimeDiff(A2,B2,Work_Hours,US_Holidays)

Named Ranges
Name	Refers To	Cells
US_Holidays	=bannianke!$J$2:$J$22	C2:C6
Work_Hours	=bannianke!$G$2:$H$9	C2:C6

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

A sample file I have stored here:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

(Please download, open and use at your own risk - but I am using an up-to-date virus scanning program)

 

[Eric W]

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]

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

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

Thanks again !!!!!!!!

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F	G	H	I
1		Received	Called	Response Time		Helper1			Holidays
2		8/22/2020 7:44	8/24/2020 11:05	2.08		8/24/2020 9:00			9/7/2020
3		8/23/2020 14:44	8/24/2020 18:35	9.58		8/24/2020 9:00			11/26/2020
4		8/21/2020 10:44	8/25/2020 9:45	19.02		8/21/2020 10:44			12/25/2020
5		8/22/2020 10:44	8/26/2020 9:45	24.75		8/24/2020 9:00
6		8/23/2020 10:44	8/27/2020 9:45	36.75		8/24/2020 9:00
7		8/24/2020 10:44	8/28/2020 9:45	47.02		8/24/2020 10:44
8		8/25/2020 10:44	8/26/2020 9:45	11.02		8/25/2020 10:44
9		8/26/2020 10:44	8/26/2020 11:45	1.02		8/26/2020 10:44
10		7/27/2020 21:44	8/26/2020 9:45	236.75		7/28/2020 9:00
11		8/7/2020 19:17	7/10/2020 9:45	Error		8/10/2020 9:00
12		8/7/2020 10:08	8/7/2020 13:45	3.62		8/7/2020 10:08
13		8/7/2020 5:44	8/7/2020 9:45	0.75		8/7/2020 9:00
14		8/7/2020 18:21	8/10/2020 11:13	2.22		8/10/2020 9:00
15		9/4/2020 16:45	9/8/2020 10:00	1.25		9/4/2020 16:45
Sheet1

Cell Formulas
Range	Formula
D2:D15	D2	=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:F15	F2	=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]

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]

Sulprobil kindly pointed out a problem with my latest Helper1 formula, it should be:

Book1
	A	B	C	D	E	F	G	H	I
1		Received	Called	Response Time		Helper1			Holidays
2		8/22/20 7:44	8/24/20 11:05	2.08		8/24/20 9:00			9/7/2020
3		8/23/20 14:44	8/24/20 18:35	9.58		8/24/20 9:00			11/26/2020
4		8/21/20 10:44	8/25/20 9:45	19.02		8/21/20 10:44			12/25/2020
5		8/22/20 10:44	8/26/20 9:45	24.75		8/24/20 9:00
6		8/23/20 10:44	8/27/20 9:45	36.75		8/24/20 9:00
7		8/24/20 10:44	8/28/20 9:45	47.02		8/24/20 10:44
8		8/25/20 10:44	8/26/20 9:45	11.02		8/25/20 10:44
9		8/26/20 10:44	8/26/20 11:45	1.02		8/26/20 10:44
10		7/27/20 21:44	8/26/20 9:45	236.75		7/28/20 9:00
11		8/7/20 19:17	7/10/20 9:45	Error		8/10/20 9:00
12		8/7/20 10:08	8/7/20 13:45	3.62		8/7/20 10:08
13		8/7/20 5:44	8/7/20 9:45	0.75		8/7/20 9:00
14		8/7/20 18:21	8/10/20 11:13	2.22		8/10/20 9:00
15		9/4/20 16:45	9/8/20 10:00	1.25		9/4/20 16:45
16		8/25/20 20:04	8/26/20 20:00	11.93		8/25/20 20:04
Sheet14

Cell Formulas
Range	Formula
D2:D16	D2	=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:F16	F2	=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]

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]

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