Calculate time difference between two times with removing non working hours

[pratik554]

Hi All,

I have been trying to fix the issue since long time but unable to get the exact required result. My requirement is as below.

1) Need to calculate difference between 2 dates and time
2) This dates may go overnight and should exclude non working hours
3) There is non working hours from 23:00 and 1:00 AM. For example - 15th April 11:00 PM to 16th April 1:00 AM. System is no available
4) All days are working and there is no holiday

Test Data.xlsx
	A	B	C	D
1	Start Date & Time	End Date & Time	New Formula TAT	Normal TAT
2	01-04-2022 07:41:25 AM	02-04-2022 08:05	00 hrs 23 mins	00 hrs 23 mins
3	01-04-2022 08:18:16 AM	02-04-2022 08:05	23 hrs 46 mins	23 hrs 46 mins
4	01-04-2022 08:18:46 AM	02-04-2022 08:05	23 hrs 46 mins	23 hrs 46 mins
5	01-04-2022 08:35:16 AM	02-04-2022 08:05	23 hrs 29 mins	23 hrs 29 mins
6	01-04-2022 08:45:37 AM	02-04-2022 08:05	23 hrs 19 mins	23 hrs 19 mins
7	01-04-2022 08:50:06 AM	02-04-2022 08:05	23 hrs 14 mins	23 hrs 14 mins
8	01-04-2022 09:12:25 AM	02-04-2022 08:05	22 hrs 52 mins	22 hrs 52 mins
9	01-04-2022 09:21:56 AM	02-04-2022 08:05	22 hrs 43 mins	22 hrs 43 mins
10	01-04-2022 09:24:45 AM	02-04-2022 08:05	22 hrs 40 mins	22 hrs 40 mins
11	01-04-2022 09:29:06 AM	02-04-2022 08:05	22 hrs 35 mins	22 hrs 35 mins
12	01-04-2022 09:30:16 AM	02-04-2022 08:05	22 hrs 34 mins	22 hrs 34 mins
13	01-04-2022 09:38:56 AM	02-04-2022 08:05	22 hrs 26 mins	22 hrs 26 mins
14	01-04-2022 09:42:06 AM	02-04-2022 08:05	22 hrs 22 mins	22 hrs 22 mins
15	01-04-2022 09:42:36 AM	02-04-2022 08:05	22 hrs 22 mins	22 hrs 22 mins
16	01-04-2022 09:49:55 AM	02-04-2022 08:05	22 hrs 15 mins	22 hrs 15 mins
17	01-04-2022 10:00:05 AM	02-04-2022 08:05	22 hrs 04 mins	22 hrs 04 mins
18	01-04-2022 10:01:06 AM	02-04-2022 08:05	22 hrs 03 mins	22 hrs 03 mins
19	01-04-2022 10:08:16 AM	02-04-2022 08:05	21 hrs 56 mins	21 hrs 56 mins
20	01-04-2022 10:10:16 AM	02-04-2022 08:05	21 hrs 54 mins	21 hrs 54 mins
21	01-04-2022 10:12:46 AM	02-04-2022 08:05	21 hrs 52 mins	21 hrs 52 mins
22	01-04-2022 10:20:02 AM	02-04-2022 08:05	21 hrs 45 mins	21 hrs 45 mins
23	01-04-2022 10:40:05 AM	02-04-2022 08:05	21 hrs 24 mins	21 hrs 24 mins
24	01-04-2022 10:41:05 AM	02-04-2022 08:05	21 hrs 23 mins	21 hrs 23 mins
25	01-04-2022 10:43:36 AM	02-04-2022 08:05	21 hrs 21 mins	21 hrs 21 mins
26	01-04-2022 10:46:15 AM	02-04-2022 08:05	21 hrs 18 mins	21 hrs 18 mins
27	01-04-2022 10:49:17 AM	02-04-2022 08:05	21 hrs 15 mins	21 hrs 15 mins
28	01-04-2022 10:49:46 AM	02-04-2022 08:05	21 hrs 15 mins	21 hrs 15 mins
29	01-04-2022 10:49:46 AM	02-04-2022 08:05	21 hrs 15 mins	21 hrs 15 mins
30	01-04-2022 11:08:36 AM	02-04-2022 08:05	20 hrs 56 mins	20 hrs 56 mins
31	01-04-2022 11:09:15 AM	02-04-2022 08:05	20 hrs 55 mins	20 hrs 55 mins
32	01-04-2022 11:12:16 AM	02-04-2022 08:05	20 hrs 52 mins	20 hrs 52 mins
33	01-04-2022 11:27:37 AM	02-04-2022 08:05	20 hrs 37 mins	20 hrs 37 mins
34	01-04-2022 11:30:47 AM	02-04-2022 08:05	20 hrs 34 mins	20 hrs 34 mins
35	01-04-2022 11:35:17 AM	02-04-2022 08:05	20 hrs 29 mins	20 hrs 29 mins
36	01-04-2022 11:39:05 AM	02-04-2022 08:05	20 hrs 26 mins	20 hrs 26 mins
37	01-04-2022 11:41:05 AM	02-04-2022 08:05	20 hrs 24 mins	20 hrs 24 mins
38	01-04-2022 11:46:37 AM	02-04-2022 08:05	20 hrs 18 mins	20 hrs 18 mins
39	01-04-2022 11:54:25 AM	02-04-2022 08:05	20 hrs 10 mins	20 hrs 10 mins
40	01-04-2022 12:00:26 PM	02-04-2022 08:05	20 hrs 04 mins	20 hrs 04 mins
41	01-04-2022 12:03:27 PM	02-04-2022 08:05	20 hrs 01 mins	20 hrs 01 mins
42	10-04-2022 11:21:39 PM	10-04-2022 23:25	00 hrs 03 mins	00 hrs 03 mins
Data

Cell Formulas
Range	Formula
C2:C42	C2	=MOD(NETWORKDAYS(A2,B2,A2)*("23:00"-"01:00")-(1-MOD(B2-A2,1)),1)
D2:D42	D2	=B2-A2

 

[pratik554]

Hi All, please help in this. This will be really helpful.

 

[pratik554]

Hi All,

Please help me in resolving this query.

 

[Soledad]

Hi All,

Please help me in resolving this

Put this formula into E2 and then copy down

=SUMPRODUCT((MOD("23:00"-"1:00",1)>MOD(A2:B2-"1:00",1))*MOD("23:00"-A2:B2,1)*{1,-1})+MOD("23:00"-"1:00",1)*((MOD(B2-A2,1)>MOD("1:00"-A2,1))+INT(B2-A2))[/CODE

 

[pratik554]

Put this formula into E2 and then copy down

=SUMPRODUCT((MOD("23:00"-"1:00",1)>MOD(A2:B2-"1:00",1))*MOD("23:00"-A2:B2,1)*{1,-1})+MOD("23:00"-"1:00",1)*((MOD(B2-A2,1)>MOD("1:00"-A2,1))+INT(B2-A2))[/CODE

Thank you for helping me in this.

I have tried the formula, however, unable to get the last 2 part of formula. What to be entered in [/CODE[/CODE].

 

[Soledad]

Thank you for helping me in this.

I have tried the formula, however, unable to get the last 2 part of formula. What to be entered in [/CODE[/CODE].

Sorry for my misstake, its exactly what below
=SUMPRODUCT((MOD("23:00"-"1:00",1)>MOD(A2:B2-"1:00",1))*MOD("23:00"-A2:B2,1)*{1,-1})+MOD("23:00"-"1:00",1)*((MOD(B2-A2,1)>MOD("1:00"-A2,1))+INT(B2-A2))

 

[pratik554]

Sorry for my misstake, its exactly what below
=SUMPRODUCT((MOD("23:00"-"1:00",1)>MOD(A2:B2-"1:00",1))*MOD("23:00"-A2:B2,1)*{1,-1})+MOD("23:00"-"1:00",1)*((MOD(B2-A2,1)>MOD("1:00"-A2,1))+INT(B2-A2))

Thank you so much!! this worked perfectly.