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

Ahmed101056

New Member
Joined
Nov 15, 2019
Messages
8
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,159
Welcome to the forum!

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">11/7/2019 11:30</td><td style="text-align: right;;">11/13/2019 9:15</td><td style="text-align: right;;">21.75</td><td style="text-align: right;;"></td><td style="text-align: right;;">11/11/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11/28/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">12/25/2019</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet12</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=IF(<font color="Blue">INT(<font color="Red">B1</font>)-INT(<font color="Red">A1</font>)<2,0,NETWORKDAYS(<font color="Red">A1+1,B1-1,E1:E3</font>)</font>)*8+(<font color="Blue">1/3-MOD(<font color="Red">A1,1</font>)+MOD(<font color="Red">B1,1</font>)</font>)*24</td></tr></tbody></table></td></tr></table><br />
 

Ahmed101056

New Member
Joined
Nov 15, 2019
Messages
8
Welcome to the forum!

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

ABCDE
111/7/2019 11:3011/13/2019 9:1521.7511/11/2019
211/28/2019
312/25/2019

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet12

Worksheet Formulas
CellFormula
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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,159
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

New Member
Joined
Nov 15, 2019
Messages
8
i may have not been clear enough i meant we have no weekend in this calculation working 365 day per year :LOL:

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,159
I'm not sure what to tell you - here's my results with the same data:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Start Date</td><td style=";">Current Time</td><td style=";">All Days Calculated</td><td style=";">Weekend Excluded</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">11/14/19 3:01 PM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">2.1</td><td style="text-align: right;;">2.1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">11/14/19 8:53 PM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">-3.76667</td><td style="text-align: right;;">-3.76667</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">11/15/19 5:00 AM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">4.116667</td><td style="text-align: right;;">4.116667</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">11/14/19 4:48 PM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">0.316667</td><td style="text-align: right;;">0.316667</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">11/14/19 7:11 PM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">-2.06667</td><td style="text-align: right;;">-2.06667</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">11/14/19 9:53 AM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">7.233333</td><td style="text-align: right;;">7.233333</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">11/14/19 7:30 PM</td><td style="text-align: right;;">11/16/2019 1:07</td><td style="text-align: right;;">-2.38333</td><td style="text-align: right;;">-2.38333</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet12</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">INT(<font color="Red">B2</font>)-INT(<font color="Red">A2</font>)<2,0,NETWORKDAYS.INTL(<font color="Red">A2+1,B2-1,"0000000"</font>)</font>)*8+(<font color="Blue">1/3-MOD(<font color="Red">A2,1</font>)+MOD(<font color="Red">B2,1</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">INT(<font color="Red">B2</font>)-INT(<font color="Red">A2</font>)<2,0,NETWORKDAYS(<font color="Red">A2+1,B2-1</font>)</font>)*8+(<font color="Blue">1/3-MOD(<font color="Red">A2,1</font>)+MOD(<font color="Red">B2,1</font>)</font>)*24</td></tr></tbody></table></td></tr></table><br />

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

New Member
Joined
Nov 15, 2019
Messages
8
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:

First Date
Current TimeShift StartShift End
Elapsed working hours
Comment
11/10/19 1:27 PM
11/16/2019 13:358:00
4:0047:08:00
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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,159
I've been busy lately and haven't had a chance to work on this much, but try this:

Book1
ABCDEF
1First DateCurrent TimeShift StartShift EndElapsed working hoursComment
211/10/2019 1:27:00 PM11/16/2019 1:35:00 PM8:0016:0047.86666667At 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
RangeFormula
E2E2=(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

New Member
Joined
Nov 15, 2019
Messages
8
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 😄😄.
 

Forum statistics

Threads
1,085,546
Messages
5,384,373
Members
401,890
Latest member
Angela7

Some videos you may like

This Week's Hot Topics

Top