Need To Get Elapsed Time Between 2 Dates During Working Hours ( All Days Included )

[Ahmed101056]

Hi

I need to get the elapsed hours between 2 dates considering that working hours is from 8 AM To 4 PM
all days included ( no week ends or holidays )

example :
A1 : 11/11/2019 09:31 AM
B1 : current time { now() } will be used

 

[Eric W]

Welcome to the forum!

There are a lot of scenarios, and I haven't tested them all, but give this a try:

Book1
	A	B	C	D	E
1	11/7/2019 11:30	11/13/2019 9:15	21.75		11/11/2019
2					11/28/2019
3					12/25/2019
Sheet12

Cell Formulas
Range	Formula
C1	=IF(INT(B1)-INT(A1)<2,0,NETWORKDAYS(A1+1,B1-1,E1:E3))*8+(1/3-MOD(A1,1)+MOD(B1,1))*24

 

[Ahmed101056]

Welcome to the forum!

There are a lot of scenarios, and I haven't tested them all, but give this a try:

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]11/7/2019 11:30[/TD]
[TD="align: right"]11/13/2019 9:15[/TD]
[TD="align: right"]21.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/11/2019[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/28/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/25/2019[/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(INT(B1)-INT(A1)<2,0,NETWORKDAYS(A1+1,B1-1,E1:E3))*8+(1/3-MOD(A1,1)+MOD(B1,1))*24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Eric for your response
i believe network day function exclude ween ends and i need to include all days in this calculation i dont want to exclude anything only the hours after 4 PM to 7:59 AM

 

[Eric W]

Your original post said "(no week ends or holidays )". If you want to include weekends, try:

=IF(INT(B1)-INT(A1)<2,0,NETWORKDAYS.INTL(A1+1,B1-1,"0000000",E1:E3))*8+(1/3-MOD(A1,1)+MOD(B1,1))*24

If you want to include holidays, remove the range in red.

 

[Ahmed101056]

i may have not been clear enough i meant we have no weekend in this calculation working 365 day per year

I've tried both equation and some how they gave me the same result which is far from the real total of working hours elapsed

 

[Eric W]

I'm not sure what to tell you - here's my results with the same data:

Book1
	A	B	C	D
1	Start Date	Current Time	All Days Calculated	Weekend Excluded
2	11/14/19 3:01 PM	11/16/2019 1:07	2.1	2.1
3	11/14/19 8:53 PM	11/16/2019 1:07	-3.76667	-3.76667
4	11/15/19 5:00 AM	11/16/2019 1:07	4.116667	4.116667
5	11/14/19 4:48 PM	11/16/2019 1:07	0.316667	0.316667
6	11/14/19 7:11 PM	11/16/2019 1:07	-2.06667	-2.06667
7	11/14/19 9:53 AM	11/16/2019 1:07	7.233333	7.233333
8	11/14/19 7:30 PM	11/16/2019 1:07	-2.38333	-2.38333
Sheet12

Cell Formulas
Range	Formula
C2	=IF(INT(B2)-INT(A2)<2,0,NETWORKDAYS.INTL(A2+1,B2-1,"0000000"))*8+(1/3-MOD(A2,1)+MOD(B2,1))*24
D2	=IF(INT(B2)-INT(A2)<2,0,NETWORKDAYS(A2+1,B2-1))*8+(1/3-MOD(A2,1)+MOD(B2,1))*24

I suspect that there's a regional setting somewhere that's throwing things off, although I don't know what since our dates seem to be the same.

Also, in your examples, most of the start dates are outside of the 8:00 AM - 4:00 PM window. That will definitely throw off the logic I used to calculate the hours. Perhaps if you showed a few examples of times and expected results, it might help.

 

[Ahmed101056]

I'm not sure what to tell you - here's my results with the same data:


I suspect that there's a regional setting somewhere that's throwing things off, although I don't know what since our dates seem to be the same.

Also, in your examples, most of the start dates are outside of the 8:00 AM - 4:00 PM window. That will definitely throw off the logic I used to calculate the hours. Perhaps if you showed a few examples of times and expected results, it might help.

well yea many of the tickets i will work on is already created outside the working hours thats why i need to exclude any time outside " 8:00 AM " to " 4:00 PM "

Example:

[TABLE="width: 1092"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]First Date
[/TD]
[TD="align: center"]Current Time[/TD]
[TD="align: center"]Shift Start[/TD]
[TD="align: center"]Shift End
[/TD]
[TD="align: center"]Elapsed working hours
[/TD]
[TD="align: center"]Comment[/TD]
[/TR]
[TR]
[TD="align: center"]11/10/19 1:27 PM
[/TD]
[TD="align: center"]11/16/2019 13:35[/TD]
[TD="align: center"]8:00
[/TD]
[TD="align: center"]4:00[/TD]
[TD="align: center"]47:08:00
[/TD]
[TD="align: center"]At 10 nov from 1:27 to 4 PM 2:33 hour and from 11 nov to 15 nov full working hours " 8 hours " 40 hour and on 16 nov fromm 8 AM to 13:35 worth 4:35 hour so the total working hours is 47 hour and 8 minutes
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ahmed101056]

 

[Eric W]

I've been busy lately and haven't had a chance to work on this much, but try this:

Book1
	A	B	C	D	E	F
1	First Date	Current Time	Shift Start	Shift End	Elapsed working hours	Comment
2	11/10/2019 1:27:00 PM	11/16/2019 1:35:00 PM	8:00	16:00	47.86666667	At 10 nov from 1:27 to 4 PM 2:33 hour and from 11 nov to 15 nov full working hours " 8 hours " 40 hour and on 16 nov fromm 8 AM to 13:35 worth 4:35 hour so the total working hours is 47 hour and 8 minutes
Sheet1

Cell Formulas
Range	Formula
E2	E2	=(INT(B2)-INT(A2)-1)*8+(D2-MEDIAN(MOD(B2,1),C2,D2)+MEDIAN(MOD(A2,1),C2,D2)-C2)*24

There's probably a better cell format for E2, but I believe the value is correct.

 

[Ahmed101056]

Thank u bro the value is perfect although i cant find suitable formatting but its ok i can deal with the number of hours as it is ??.