exclude non-business hours and weekends

[KARANDREA]

Thanks in advance for any help that can be offered.
I need to calculate the time between Assign date & Owned Date, but i must exclude weekends, if exists, and non business hours.
I am using ths formula at G column :
=(NETWORKDAYS(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);E2)-1+MOD(E2;1)-MOD(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);1))
It exclude weekends and start counting from Next business day from 8:00.

But i have different support hours, as example line 9, so i need to modify my formula with business hours in columns B & C.

I tried this formula in column H
=(NETWORKDAYS(D2;E2)-1)*(C2-B2)+IF(NETWORKDAYS(E2;E2);MEDIAN(MOD(E2;1);C2;B2);C2)-MEDIAN(NETWORKDAYS(D2;D2)*MOD(D2;1);C2;B2)
but i can't find out how change it in order start counting from Next business day. As example it gives me wrong result in cell H3.

I am confused.

Please help!

 

[yky]

@yky - I really can't understand what is going on. I am copying your formula as it is.

Not sure what's going on. I just copied your table and pasted it to Excel. I then copied C2 and pasted it by value. It appeared as 15/1/2020 13:44 (see J2), not a number. So, it is not a real date. It should never have worked for you. I don't know why you still got some correct results.

I did the same for my table and it worked, meaning dates are real dates, not text.

Another peculiar thing I found is the correct result. They have become huge numbers. This is the second time I notice it. Don't know what's going on.

hours.xlsx
	A	B	C	D	E	F	G	H	I	J
1	SUPPORT START TIME	SUPORT END TIME	Assign Date	Owned Date		correct result	first formula result	jasonb75	yky
2	0	0	15/1/2020 13:44	15/1/2020 14:00		1.11E+12	#VALUE!	#VALUE!	#VALUE!	15/1/2020 13:44
3	0	0	########	13/1/2020 09:16		5.28E+12	#VALUE!	#VALUE!	#VALUE!
Sheet3

 

[KARANDREA]

You are right !! A & B are text. I changed them and now i get the correct results.

Of course I can't understand your formula - I am lost in it - but it seems to work!
I will check it for all inserts i have!
Thank you very much!

 

[jasonb75]

You are right !! A & B are text. I changed them and now i get the correct results.

That may have also been the problem with my much shorter formula. The problem that @yky was seeing by copying your table was more likely caused by pasting dd/mm/yy format dates into a mm/dd/yy format computer.

As I mentioned in the post with the formula, it was based on an assumption, if my assumption was correct then the formula should work correctly.

 

[KARANDREA]

Thank you again! Both formula worked fine when I corrected the time as "time " and not as "text".
I tried both of them in my file.
I have some cases that are in lines 24 & 26 - 31 that i don't have the correct result.

XFProjects test.xlsx
	B	C	D	E		G	H	I
1	SUPPORT START TIME	SUPORT END TIME	Assign Date	Owned Date		correct result	yky	jasonb75
2	08:00	18:00	15/1/20 13:44	15/1/20 14:00		0:16	0:16	0:16
3	08:00	18:00	11/1/20 18:54	13/1/20 9:16		1:16	1:16	1:16
4	08:00	18:00	10/1/20 17:03	10/1/20 17:05		0:02	0:02	0:02
5	08:00	18:00	16/1/20 10:30	16/1/20 10:43		0:13	0:13	0:13
6	08:00	18:00	14/1/20 13:28	14/1/20 13:30		0:02	0:02	0:02
7	08:00	18:00	17/1/20 13:48	17/1/20 14:06		0:18	0:18	0:18
8	08:00	18:00	20/1/20 9:49	20/1/20 9:58		0:09	0:09	0:09
9	09:00	18:00	27/1/20 13:23	27/1/20 15:17		1:54	1:54	1:54
10	08:00	18:00	31/1/20 15:27	31/1/20 15:31		0:04	0:04	0:04
11	08:00	18:00	1/2/20 10:43	3/2/20 8:23		0:23	0:23	0:23
12	08:00	18:00	3/2/20 13:19	3/2/20 13:31		0:12	0:12	0:12
13	08:00	18:00	8/2/20 16:26	10/2/20 8:28		0:28	0:28	0:28
14	08:00	18:00	6/3/20 18:46	9/3/20 10:15		2:15	2:15	2:15
15	08:00	18:00	8/5/20 9:06	8/5/20 9:36		0:30	0:30	0:30
16	08:00	18:00	11/5/20 14:38	11/5/20 15:26		0:48	0:48	0:48
17	08:00	18:00	11/5/20 14:33	11/5/20 15:27		0:54	0:54	0:54
18	08:00	18:00	2/3/20 19:11	2/3/20 20:22		0:00	0:00	0:00
19	08:00	18:00	2/3/20 19:09	2/3/20 20:24		0:00	0:00	0:00
20	08:00	18:00	19/5/20 21:11	20/5/20 10:55		2:55	2:55	2:55
21	08:00	18:00	18/5/20 16:33	18/5/20 16:42		0:09	0:09	0:09
22	08:00	18:00	18/5/20 16:32	18/5/20 16:42		0:10	0:10	0:10
23	08:00	18:00	18/5/20 19:57	18/5/20 21:14		0:00	0:00	0:00
24	08:00	18:00	18/5/20 7:55	18/5/20 18:10		10:00	10:00	10:15
25	08:00	18:00	18/5/20 9:55	18/5/20 17:00		7:05	7:05	7:05
26	08:00	18:00	20/2/2020 16:10	21/2/2020 08:13		2:03	16:03	0:13
27	08:00	18:00	2/3/2020 11:55	3/3/2020 08:38		6:43	20:43	0:38
28	08:00	18:00	3/3/2020 17:31	4/3/2020 09:29		1:58	15:58	1:29
29	08:00	18:00	3/3/2020 12:08	4/3/2020 09:30		7:22	21:22	1:30
30	08:00	18:00	10/3/2020 17:12	11/3/2020 08:34		1:22	15:22	0:34
31	08:00	18:00	26/6/2020 17:48	29/6/2020 09:20		1:32	11:32	1:20
all

 

[KARANDREA]

I tried again one of my first formula and works! from the begining the problem was from the cell type...

=(NETWORKDAYS(D2;E2)-1)*(C2-B2)+IF(NETWORKDAYS(E2;E2);MEDIAN(MOD(E2;1);C2;B2);C2)-MEDIAN(NETWORKDAYS(D2;D2)*MOD(D2;1);C2;B2)

 

[KARANDREA]

Thank you very much for your time

XFProjects test.xlsx
	B	C	D	E		G	H	I	J
1	SUPPORT START TIME	SUPORT END TIME	Assign Date	Owned Date		correct result	yky	jasonb75	new try
2	08:00	18:00	15/1/20 13:44	15/1/20 14:00		0:16	0:16	0:16	0:16
3	08:00	18:00	11/1/20 18:54	13/1/20 9:16		1:16	1:16	1:16	1:16
4	08:00	18:00	10/1/20 17:03	10/1/20 17:05		0:02	0:02	0:02	0:02
5	08:00	18:00	16/1/20 10:30	16/1/20 10:43		0:13	0:13	0:13	0:13
6	08:00	18:00	14/1/20 13:28	14/1/20 13:30		0:02	0:02	0:02	0:02
7	08:00	18:00	17/1/20 13:48	17/1/20 14:06		0:18	0:18	0:18	0:18
8	08:00	18:00	20/1/20 9:49	20/1/20 9:58		0:09	0:09	0:09	0:09
9	09:00	18:00	27/1/20 13:23	27/1/20 15:17		1:54	1:54	1:54	1:54
10	08:00	18:00	31/1/20 15:27	31/1/20 15:31		0:04	0:04	0:04	0:04
11	08:00	18:00	1/2/20 10:43	3/2/20 8:23		0:23	0:23	0:23	0:23
12	08:00	18:00	3/2/20 13:19	3/2/20 13:31		0:12	0:12	0:12	0:12
13	08:00	18:00	8/2/20 16:26	10/2/20 8:28		0:28	0:28	0:28	0:28
14	08:00	18:00	6/3/20 18:46	9/3/20 10:15		2:15	2:15	2:15	2:15
15	08:00	18:00	8/5/20 9:06	8/5/20 9:36		0:30	0:30	0:30	0:30
16	08:00	18:00	11/5/20 14:38	11/5/20 15:26		0:48	0:48	0:48	0:48
17	08:00	18:00	11/5/20 14:33	11/5/20 15:27		0:54	0:54	0:54	0:54
18	08:00	18:00	2/3/20 19:11	2/3/20 20:22		0:00	0:00	0:00	0:00
19	08:00	18:00	2/3/20 19:09	2/3/20 20:24		0:00	0:00	0:00	0:00
20	08:00	18:00	19/5/20 21:11	20/5/20 10:55		2:55	2:55	2:55	2:55
21	08:00	18:00	18/5/20 16:33	18/5/20 16:42		0:09	0:09	0:09	0:09
22	08:00	18:00	18/5/20 16:32	18/5/20 16:42		0:10	0:10	0:10	0:10
23	08:00	18:00	18/5/20 19:57	18/5/20 21:14		0:00	0:00	0:00	0:00
24	08:00	18:00	18/5/20 7:55	18/5/20 18:10		10:00	10:00	10:15	10:00
25	08:00	18:00	18/5/20 9:55	18/5/20 17:00		7:05	7:05	7:05	7:05
26	08:00	18:00	20/2/20 16:10	21/2/20 8:13		2:03	16:03	2:03	2:03
27	08:00	18:00	2/3/20 11:55	3/3/20 8:38		6:43	20:43	6:43	6:43
28	08:00	18:00	3/3/20 17:31	4/3/20 9:29		1:58	15:58	1:58	1:58
29	08:00	18:00	3/3/20 12:08	4/3/20 9:30		7:22	21:22	7:22	7:22
30	08:00	18:00	10/3/20 17:12	11/3/20 8:34		1:22	15:22	1:22	1:22
31	08:00	18:00	26/6/20 17:48	29/6/20 9:20		1:32	11:32	1:32	1:32
all

 

[yky]

Thank you again! Both formula worked fine when I corrected the time as "time " and not as "text".
I tried both of them in my file.
I have some cases that are in lines 24 & 26 - 31 that i don't have the correct result.

Again, some of the time are TEXT and some are real time. I don't know how you manage to work with them.