date/time difference for support calls with workday, opening times & holiday functions

[gjenk]

Hi Guys

I have a spreadsheet logging support calls which has a start date/time (raised on) & end date/time (resolved on) which I want to be able to calculate the time taken to resolve the call based on daily opening time of 8:40-17:00 excluding weekends & holidays. Also want to be able to group the time taken to resolve call in 15 mins, 2hrs, 1 day, 2 days &more that 2 days.

Reference	Summary	Owner	Customer	SUPPORT START TIME	SUPORT END TIME	Raised On	Resolved On	Resolution time
IN00020066	VPN wont connect	Derek Green	Suzanne Preston	08:40​	17:00​	06/12/2021 13:34​	07/02/2022 13:16​
IN00020147	365 Password reset	Derek Green	Sean OSullivan	08:40​	17:00​	17/12/2021 09:18​	07/02/2022 13:17​
IN00020170	VMWare Tools Update	Matt Beale	Matt Beale	08:40​	17:00​	20/12/2021 09:39​	02/02/2022 22:44​
IN00020345	make a point live in office	Matt Beale	Frances Laughlin	08:40​	17:00​	12/01/2022 16:15​	02/02/2022 11:42​
IN00020350	Print room copier	Corinna Milligan	Alison Beattie	08:40​	17:00​	13/01/2022 10:46​	14/02/2022 09:25​
IN00020374	Authenticator- Pauline Brennan	Derek Green	Alison Beattie	08:40​	17:00​	17/01/2022 11:28​	07/02/2022 13:15​
IN00020462	Purchase 3x Tablets	Yvonne ODonovan	Rachel Ritchie	08:40​	17:00​	24/01/2022 14:08​	07/02/2022 16:02​
IN00020477	Laptop Required - Quote Needed	Matt Beale	Tracy James	08:40​	17:00​	25/01/2022 14:22​	04/02/2022 12:13​
IN00020479	Additional Wifi - ECP	Corinna Milligan	Lloyd BainesDavies	08:40​	17:00​	25/01/2022 15:31​	08/02/2022 09:55​
IN00020517	George Brassington	Robert Hall	Angela Ross	08:40​	17:00​	27/01/2022 16:09​	08/02/2022 14:03​
IN00020532	printer at golf course	Matt Beale	Adam Smith	08:40​	17:00​	28/01/2022 13:08​	21/02/2022 10:57​
IN00020534	Imail locked out	Yvonne ODonovan	Donna Harrison	08:40​	17:00​	28/01/2022 15:35​	01/02/2022 08:32​
IN00020535	I keep getting thrown out of my TEAM meetings by the system	Derek Green	Janice Parker	08:40​	17:00​	28/01/2022 17:48​	11/02/2022 08:41​
IN00020542	PoE Switch - Log Cabin Project	Matt Beale	Lloyd BainesDavies	08:40​	17:00​	31/01/2022 10:56​	03/02/2022 10:24​
IN00020544	Kofax Scanning in Benefits not recognising the headers	Derek Green	Lindsey Holt	08:40​	17:00​	31/01/2022 13:12​	01/02/2022 09:30​
IN00020551	ql not working on lap top	Derek Green	Katie McHutchison	08:40​	17:00​	31/01/2022 14:53​	01/02/2022 11:08​
IN00020552	Desk phone / network connection not working	Peter Connelly	Gemma ODonovan	08:40​	17:00​	31/01/2022 15:43​	01/02/2022 09:12​
IN00020553	Fwd: Our Office - Toner	Derek Green	Alison Beattie	08:40​	17:00​	31/01/2022 16:30​	01/02/2022 13:41​
IN00020554	QL4	Yvonne ODonovan	Robert Browne	08:40​	17:00​	31/01/2022 18:21​	02/02/2022 08:10​
IN00020556	QL not working	Yvonne ODonovan	Lisa Madden	08:40​	17:00​	01/02/2022 08:58​	01/02/2022 10:14​

 

[6StringJazzer]

=F2-MOD(G2,1)+MOD(H2,1)-E2+NETWORKDAYS(INT(G2)+1,INT(H2)-1,HolidayList)*(F2-E2)

Create a named range with your list of holiday dates and call it Holiday List.

Use a custom format for Resolution time:
[h]:mm;@

Your data pastes in as text instead of dates/times so my testing was very limited. You will need to test this on your own data.

 

[gjenk]

Here is the spreadsheet with your formula. I have set the format of the cells as [h]:mm;@ There seems to be an issue with some of the results - see below:

Support calls.xlsx
	A	B	C	D	E	F	G	H	I
1	Reference	Summary	Owner	Customer	SUPPORT START TIME	SUPORT END TIME	Raised On	Resolved On	Resolution time
2	IN00020066	VPN wont connect	Derek Green	Suzanne Preston	08:40	17:00	06/12/2021 13:34	07/02/2022 13:16	349:42
3	IN00020147	365 Password reset	Derek Green	Sean OSullivan	08:40	17:00	17/12/2021 09:18	07/02/2022 13:17	278:58
4	IN00020170	VMWare Tools Update	Matt Beale	Matt Beale	08:40	17:00	20/12/2021 09:39	02/02/2022 22:44	254:45
5	IN00020345	make a point live in office	Matt Beale	Frances Laughlin	08:40	17:00	12/01/2022 16:15	02/02/2022 11:42	120:26
6	IN00020350	Print room copier	Corinna Milligan	Alison Beattie	08:40	17:00	13/01/2022 10:46	14/02/2022 09:25	181:58
7	IN00020374	Authenticator- Pauline Brennan	Derek Green	Alison Beattie	08:40	17:00	17/01/2022 11:28	07/02/2022 13:15	126:46
8	IN00020462	Purchase 3x Tablets	Yvonne ODonovan	Rachel Ritchie	08:40	17:00	24/01/2022 14:08	07/02/2022 16:02	85:14
9	IN00020477	Laptop Required - Quote Needed	Matt Beale	Tracy James	08:40	17:00	25/01/2022 14:22	04/02/2022 12:13	64:30
10	IN00020479	Additional Wifi - ECP	Corinna Milligan	Lloyd BainesDavies	08:40	17:00	25/01/2022 15:31	08/02/2022 09:55	77:44
11	IN00020517	George Brassington	Robert Hall	Angela Ross	08:40	17:00	27/01/2022 16:09	08/02/2022 14:03	64:33
12	IN00020532	printer at golf course	Matt Beale	Adam Smith	08:40	17:00	28/01/2022 13:08	21/02/2022 10:57	131:08
13	IN00020534	Imail locked out	Yvonne ODonovan	Donna Harrison	08:40	17:00	28/01/2022 15:35	01/02/2022 08:32	9:37
14	IN00020535	I keep getting thrown out of my TEAM meetings by the system	Derek Green	Janice Parker	08:40	17:00	28/01/2022 17:48	11/02/2022 08:41	74:13
15	IN00020542	PoE Switch - Log Cabin Project	Matt Beale	Lloyd BainesDavies	08:40	17:00	31/01/2022 10:56	03/02/2022 10:24	24:28
16	IN00020544	Kofax Scanning in Benefits not recognising the headers	Derek Green	Lindsey Holt	08:40	17:00	31/01/2022 13:12	01/02/2022 09:30	####################
17	IN00020551	ql not working on lap top	Derek Green	Katie McHutchison	08:40	17:00	31/01/2022 14:53	01/02/2022 11:08	####################
18	IN00020552	Desk phone / network connection not working	Peter Connelly	Gemma ODonovan	08:40	17:00	31/01/2022 15:43	01/02/2022 09:12	####################
19	IN00020553	Fwd: Our Office - Toner	Derek Green	Alison Beattie	08:40	17:00	31/01/2022 16:30	01/02/2022 13:41	####################
20	IN00020554	QL4	Yvonne ODonovan	Robert Browne	08:40	17:00	31/01/2022 18:21	02/02/2022 08:10	6:29
21	IN00020556	QL not working	Yvonne ODonovan	Lisa Madden	08:40	17:00	01/02/2022 08:58	01/02/2022 10:14	####################
22	IN00020557	Plotting Sheet Layers in Land Charges GIS Maps	Tibor Borzasi	Tracy James	08:40	17:00	01/02/2022 09:11	02/02/2022 15:32	####################
23	IN00020559	Mail not updating on Outlook	Robert Hall		08:40	17:00	01/02/2022 10:00	01/02/2022 11:01	####################
24	IN00020561	QL won't open	Derek Green	Leah Kelly	08:40	17:00	01/02/2022 10:11	01/02/2022 16:06	####################
25	IN00020562	Failure to access Outlook/J drive etc	Derek Green	Lynn Stewart	08:40	17:00	01/02/2022 11:04	01/02/2022 15:19	####################
26	IN00020564	Unable to obtain connection to Softphone	Peter Connelly	Suzanne Wilson	08:40	17:00	01/02/2022 12:17	01/02/2022 14:29	####################
27	IN00020565	Laptop will not connect to internet	Peter Connelly	Sharon Eaton	08:40	17:00	01/02/2022 15:41	01/02/2022 16:38	####################
28	IN00020566	My Personal (psimp) H: drive does not load anything up I have saved past Oct 2020	Robert Hall	Paul Simpson	08:40	17:00	01/02/2022 16:12	03/02/2022 11:40	12:07
29	IN00020567	Customer has advised that the LLS email address is wring on the NNC website	Derek Green	Natalie Ratledge	08:40	17:00	02/02/2022 08:27	02/02/2022 08:58	####################
30	IN00020570	CONNECTIVITY	Peter Connelly	Suzanne Elliott	08:40	17:00	02/02/2022 09:26	11/02/2022 08:38	57:32
31	IN00020571	Unable to log on to Rainbow	Yvonne ODonovan	Julie Collins	08:40	17:00	02/02/2022 09:30	02/02/2022 09:34	####################
32	IN00020572	Babatunde Aregbesola vpn not working SSL error	Yvonne ODonovan	Babatunde Aregbesola	08:40	17:00	02/02/2022 09:31	02/02/2022 10:39	####################
33	IN00020573	outlook is saying there's a folder full and now I cannot delete anything	Peter Connelly	James French	08:40	17:00	02/02/2022 09:31	02/02/2022 15:57	####################
34	IN00020574	George Brassington	Derek Green	Angela Ross	08:40	17:00	02/02/2022 09:51	02/02/2022 09:59	####################
35	IN00020575	Leisha Murphy can't login to QL	Robert Hall	Leisha Murphy	08:40	17:00	02/02/2022 09:57	03/02/2022 08:32	####################
36	IN00020576	Unable to sign in to Outlook needs QR code	Yvonne ODonovan	Gary Chisholm	08:40	17:00	02/02/2022 11:15	02/02/2022 11:48	####################
37	IN00020577	Cannot access adelante payments	Yvonne ODonovan	Theresia Ellis	08:40	17:00	02/02/2022 11:41	02/02/2022 11:44	####################
38	IN00020579	Cannot generate documents on Uniform	Tibor Borzasi	Barry Agnew	08:40	17:00	02/02/2022 11:47	02/02/2022 12:06	####################
39	IN00020580	Babatunde	Robert Hall	Babatunde Aregbesola	08:40	17:00	02/02/2022 12:34	02/02/2022 13:01	####################
40	IN00020581	QL not working on shared PC, 7JMXZS2	Yvonne ODonovan	Darren Foster	08:40	17:00	02/02/2022 13:00	08/02/2022 07:45	28:05
41	IN00020582	headset	Yvonne ODonovan	Lijana Armalaite	08:40	17:00	02/02/2022 14:13	02/02/2022 15:25	####################
42	IN00020583	My H drive is not working	Peter Connelly	Amanda King	08:40	17:00	02/02/2022 14:34	10/02/2022 11:49	47:14
43	IN00020585	my works mobile phone is not taking incoming calls, it makes a high pitch noise and the screen goes blank.	Yvonne ODonovan	Daniel Boon	08:40	17:00	02/02/2022 14:47	24/02/2022 12:27	130:59
44	IN00020589	ArcReader GIS not working	Matt Beale	Tracy James	08:40	17:00	03/02/2022 08:25	03/02/2022 10:24	####################
45	IN00020590	Unable to open pdf	Peter Connelly	Colin Cox	08:40	17:00	03/02/2022 08:53	03/02/2022 09:12	####################
46	IN00020592	2x Docks, 2x Monitors	Matt Beale	Marion Williams	08:40	17:00	03/02/2022 11:18	10/02/2022 08:56	39:17
47	IN00020593	Aadil Essa	Yvonne ODonovan	Angela Ross	08:40	17:00	03/02/2022 13:02	07/02/2022 16:08	19:46
48	IN00020595	I can't believe I'm saying this, but Outlook doesn't work. Again	Matt Beale	James French	08:40	17:00	03/02/2022 14:33	03/02/2022 22:42	####################
Support calls

Cell Formulas
Range	Formula
I2:I48	I2	=F2-MOD(G2,1)+MOD(H2,1)-E2+NETWORKDAYS(INT(G2)+1,INT(H2)-1,holidays)*(F2-E2)

Named Ranges
Name	Refers To	Cells
holidays	=Holidays!$A$2:$A$25	I2:I48

 

[6StringJazzer]

Here is a revision to the formula that fixes two problems:

$scratch.xlsm
	A	B	C	D	E	F	G	H	I
1	Reference	Summary	Owner	Customer	SUPPORT START TIME	SUPORT END TIME	Raised On	Resolved On	Resolution time
2	IN00020066	VPN wont connect	Derek Green	Suzanne Preston	8:40	17:00	06/12/2021 13:34	07/02/2022 13:16	366:22
3	IN00020147	365 Password reset	Derek Green	Sean OSullivan	8:40	17:00	17/12/2021 09:18	07/02/2022 13:17	295:38
4	IN00020170	VMWare Tools Update	Matt Beale	Matt Beale	8:40	17:00	20/12/2021 09:39	02/02/2022 22:44	271:25
5	IN00020345	make a point live in office	Matt Beale	Frances Laughlin	8:40	17:00	12/01/2022 16:15	02/02/2022 11:42	120:26
6	IN00020350	Print room copier	Corinna Milligan	Alison Beattie	8:40	17:00	13/01/2022 10:46	14/02/2022 09:25	181:58
7	IN00020374	Authenticator- Pauline Brennan	Derek Green	Alison Beattie	8:40	17:00	17/01/2022 11:28	07/02/2022 13:15	126:46
8	IN00020462	Purchase 3x Tablets	Yvonne ODonovan	Rachel Ritchie	8:40	17:00	24/01/2022 14:08	07/02/2022 16:02	85:14
9	IN00020477	Laptop Required - Quote Needed	Matt Beale	Tracy James	8:40	17:00	25/01/2022 14:22	04/02/2022 12:13	64:30
10	IN00020479	Additional Wifi - ECP	Corinna Milligan	Lloyd BainesDavies	8:40	17:00	25/01/2022 15:31	08/02/2022 09:55	77:44
11	IN00020517	George Brassington	Robert Hall	Angela Ross	8:40	17:00	27/01/2022 16:09	08/02/2022 14:03	64:33
12	IN00020532	printer at golf course	Matt Beale	Adam Smith	8:40	17:00	28/01/2022 13:08	21/02/2022 10:57	131:08
13	IN00020534	Imail locked out	Yvonne ODonovan	Donna Harrison	8:40	17:00	28/01/2022 15:35	01/02/2022 08:32	9:37
14	IN00020535	I keep getting thrown out of my TEAM meetings by the system	Derek Green	Janice Parker	8:40	17:00	28/01/2022 17:48	11/02/2022 08:41	74:13
15	IN00020542	PoE Switch - Log Cabin Project	Matt Beale	Lloyd BainesDavies	8:40	17:00	31/01/2022 10:56	03/02/2022 10:24	24:28
16	IN00020544	Kofax Scanning in Benefits not recognising the headers	Derek Green	Lindsey Holt	8:40	17:00	31/01/2022 13:12	01/02/2022 09:30	4:38
17	IN00020551	ql not working on lap top	Derek Green	Katie McHutchison	8:40	17:00	31/01/2022 14:53	01/02/2022 11:08	4:35
18	IN00020552	Desk phone / network connection not working	Peter Connelly	Gemma ODonovan	8:40	17:00	31/01/2022 15:43	01/02/2022 09:12	1:49
19	IN00020553	Fwd: Our Office - Toner	Derek Green	Alison Beattie	8:40	17:00	31/01/2022 16:30	01/02/2022 13:41	5:30
20	IN00020554	QL4	Yvonne ODonovan	Robert Browne	8:40	17:00	31/01/2022 18:21	02/02/2022 08:10	6:29
21	IN00020556	QL not working	Yvonne ODonovan	Lisa Madden	8:40	17:00	01/02/2022 08:58	01/02/2022 10:14	1:16
22	IN00020557	Plotting Sheet Layers in Land Charges GIS Maps	Tibor Borzasi	Tracy James	8:40	17:00	01/02/2022 09:11	02/02/2022 15:32	14:40
23	IN00020559	Mail not updating on Outlook	Robert Hall		8:40	17:00	01/02/2022 10:00	01/02/2022 11:01	1:01
24	IN00020561	QL won't open	Derek Green	Leah Kelly	8:40	17:00	01/02/2022 10:11	01/02/2022 16:06	5:54
25	IN00020562	Failure to access Outlook/J drive etc	Derek Green	Lynn Stewart	8:40	17:00	01/02/2022 11:04	01/02/2022 15:19	4:15
26	IN00020564	Unable to obtain connection to Softphone	Peter Connelly	Suzanne Wilson	8:40	17:00	01/02/2022 12:17	01/02/2022 14:29	2:12
27	IN00020565	Laptop will not connect to internet	Peter Connelly	Sharon Eaton	8:40	17:00	01/02/2022 15:41	01/02/2022 16:38	0:57
28	IN00020566	My Personal (psimp) H: drive does not load anything up I have saved past Oct 2020	Robert Hall	Paul Simpson	8:40	17:00	01/02/2022 16:12	03/02/2022 11:40	12:07
29	IN00020567	Customer has advised that the LLS email address is wring on the NNC website	Derek Green	Natalie Ratledge	8:40	17:00	02/02/2022 08:27	02/02/2022 08:58	0:30
30	IN00020570	CONNECTIVITY	Peter Connelly	Suzanne Elliott	8:40	17:00	02/02/2022 09:26	11/02/2022 08:38	57:32
31	IN00020571	Unable to log on to Rainbow	Yvonne ODonovan	Julie Collins	8:40	17:00	02/02/2022 09:30	02/02/2022 09:34	0:03
32	IN00020572	Babatunde Aregbesola vpn not working SSL error	Yvonne ODonovan	Babatunde Aregbesola	8:40	17:00	02/02/2022 09:31	02/02/2022 10:39	1:08
33	IN00020573	outlook is saying there's a folder full and now I cannot delete anything	Peter Connelly	James French	8:40	17:00	02/02/2022 09:31	02/02/2022 15:57	6:25
34	IN00020574	George Brassington	Derek Green	Angela Ross	8:40	17:00	02/02/2022 09:51	02/02/2022 09:59	0:07
35	IN00020575	Leisha Murphy can't login to QL	Robert Hall	Leisha Murphy	8:40	17:00	02/02/2022 09:57	03/02/2022 08:32	6:55
36	IN00020576	Unable to sign in to Outlook needs QR code	Yvonne ODonovan	Gary Chisholm	8:40	17:00	02/02/2022 11:15	02/02/2022 11:48	0:33
37	IN00020577	Cannot access adelante payments	Yvonne ODonovan	Theresia Ellis	8:40	17:00	02/02/2022 11:41	02/02/2022 11:44	0:02
38	IN00020579	Cannot generate documents on Uniform	Tibor Borzasi	Barry Agnew	8:40	17:00	02/02/2022 11:47	02/02/2022 12:06	0:18
39	IN00020580	Babatunde	Robert Hall	Babatunde Aregbesola	8:40	17:00	02/02/2022 12:34	02/02/2022 13:01	0:27
40	IN00020581	QL not working on shared PC, 7JMXZS2	Yvonne ODonovan	Darren Foster	8:40	17:00	02/02/2022 13:00	08/02/2022 07:45	28:05
41	IN00020582	headset	Yvonne ODonovan	Lijana Armalaite	8:40	17:00	02/02/2022 14:13	02/02/2022 15:25	1:12
42	IN00020583	My H drive is not working	Peter Connelly	Amanda King	8:40	17:00	02/02/2022 14:34	10/02/2022 11:49	47:14
43	IN00020585	my works mobile phone is not taking incoming calls, it makes a high pitch noise and the screen goes blank.	Yvonne ODonovan	Daniel Boon	8:40	17:00	02/02/2022 14:47	24/02/2022 12:27	130:59
44	IN00020589	ArcReader GIS not working	Matt Beale	Tracy James	8:40	17:00	03/02/2022 08:25	03/02/2022 10:24	1:59
45	IN00020590	Unable to open pdf	Peter Connelly	Colin Cox	8:40	17:00	03/02/2022 08:53	03/02/2022 09:12	0:18
46	IN00020592	2x Docks, 2x Monitors	Matt Beale	Marion Williams	8:40	17:00	03/02/2022 11:18	10/02/2022 08:56	39:17
47	IN00020593	Aadil Essa	Yvonne ODonovan	Angela Ross	8:40	17:00	03/02/2022 13:02	07/02/2022 16:08	19:46
48	IN00020595	I can't believe I'm saying this, but Outlook doesn't work. Again	Matt Beale	James French	8:40	17:00	03/02/2022 14:33	03/02/2022 22:42	8:08
Resolution TIme

Cell Formulas
Range	Formula
I2:I48	I2	=IF(INT(G2)=INT(H2),H2-G2,F2-MOD(G2,1)+MOD(H2,1)-E2+MAX(0,NETWORKDAYS(INT(G2)+1,INT(H2)-1,holidays))*(F2-E2))

Named Ranges
Name	Refers To	Cells
holidays	='Resolution TIme'!$K$2:$K$3	I2:I48