Calculating elapsed hours with weekends.

MasterK

New Member
I'm trying to calculate elapsed time in hours from a start date/time to an end date/time.

Work hours are M-F, 6AM - 6PM.

Below is the formula I'm using.

=(NETWORKDAYS(B3,C3)-1)*("18:00"-"6:00")+MOD(C3,1)-MOD(B3,1)

The problem I'm having is if the start day is after business hours or on Saturday or Sunday, The hours don't calculate correctly.

I need your help with a formula correction that will resolve the scenarios below in RED

Start DateEnd DateElapsed TimeCommentCorrect Value
8/17/19 15:30 (Sun)

<tbody>
</tbody>
8/19/19 17:30 (Mon)

<tbody>
</tbody>
2:00Incorrect value11:30 hrs
8/14/19 18:30 (Wed)

<tbody>
</tbody>
8/15/19 17:30 (Thu)

<tbody>
</tbody>
11:00Incorrect Value11:30 hrs
8/14/19 7:00 (Wed)

<tbody>
</tbody>
8/16/19 13:00 (Fri)

<tbody>
</tbody>
30:00Correct30:00 hrs

<tbody>
</tbody>


Thanks much for your assistance in advance.
 

James006

Well-known Member
Hello,

Not sure to understand your question ... since Networkdays is designed to exclude week-ends ...:wink:
 

MasterK

New Member
Hello,

Not sure to understand your question ... since Networkdays is designed to exclude week-ends ...:wink:
The problem comes when the start date is on a weekend our outside of the designated work hours.
What I'm tracking is specific reports being reconciled within a shipment departure time.
The departures often occur on weekends or the early hours of the morning. My staff it not responsible
for the elapsed hour during non-business days/times. But, I can't stop the shipments from leaving
during those times.
 

Scott T

Well-known Member
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 /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">8/17/2019 15:30</td><td style="text-align: right;;">8/19/2019 17:30</td><td style="text-align: right;;">11:30:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">8/14/2019 18:30</td><td style="text-align: right;;">8/15/2019 17:30</td><td style="text-align: right;;">11:30:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">8/14/2019 7:00</td><td style="text-align: right;;">8/16/2019 13:00</td><td style="text-align: right;;">30:00:00</td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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)">D3</th><td style="text-align:left">=(<font color="Blue">NETWORKDAYS(<font color="Red">IF(<font color="Green">WEEKDAY(<font color="Purple">B3,2</font>)=6,INT(<font color="Purple">B3</font>)+2+0.25,IF(<font color="Purple">OR(<font color="Teal">WEEKDAY(<font color="#FF00FF">B3,2</font>)=7,MOD(<font color="#FF00FF">B3,1</font>)>0.75</font>),INT(<font color="Teal">B3</font>)+1+0.25,B3</font>)</font>),C3</font>)-1</font>)*(<font color="Blue">"18:00"-"6:00"</font>)+MOD(<font color="Blue">C3,1</font>)-MOD(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B3,2</font>)=6,INT(<font color="Green">B3</font>)+2+0.25,IF(<font color="Green">OR(<font color="Purple">WEEKDAY(<font color="Teal">B3,2</font>)=7,MOD(<font color="Teal">B3,1</font>)>0.75</font>),INT(<font color="Purple">B3</font>)+1+0.25,B3</font>)</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
 

MasterK

New Member
Try
BCD
38/17/2019 15:308/19/2019 17:3011:30:00
48/14/2019 18:308/15/2019 17:3011:30:00
58/14/2019 7:008/16/2019 13:0030:00:00

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

Worksheet Formulas
CellFormula
D3=(NETWORKDAYS(IF(WEEKDAY(B3,2)=6,INT(B3)+2+0.25,IF(OR(WEEKDAY(B3,2)=7,MOD(B3,1)>0.75),INT(B3)+1+0.25,B3)),C3)-1)*("18:00"-"6:00")+MOD(C3,1)-MOD(IF(WEEKDAY(B3,2)=6,INT(B3)+2+0.25,IF(OR(WEEKDAY(B3,2)=7,MOD(B3,1)>0.75),INT(B3)+1+0.25,B3)),1)

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

<tbody>
</tbody>
Amazing, thank you !
 

Scott T

Well-known Member
Forgot to account for if it is a work day but before 6AM this should cover that.

<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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">8/17/2019 15:30</td><td style="text-align: right;;">8/19/2019 17:30</td><td style="text-align: right;;">11:30:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">8/14/2019 18:30</td><td style="text-align: right;;">8/15/2019 17:30</td><td style="text-align: right;;">11:30:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">8/14/2019 7:00</td><td style="text-align: right;;">8/16/2019 13:00</td><td style="text-align: right;;">30:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">8/22/2019 5:00</td><td style="text-align: right;;">8/22/2019 7:00</td><td style="text-align: right;;">1:00:00</td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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">=(<font color="Blue">NETWORKDAYS(<font color="Red">IF(<font color="Green">WEEKDAY(<font color="Purple">A2,2</font>)=6,INT(<font color="Purple">A2</font>)+2+0.25,IF(<font color="Purple">OR(<font color="Teal">WEEKDAY(<font color="#FF00FF">A2,2</font>)=7,MOD(<font color="#FF00FF">A2,1</font>)>0.75</font>),INT(<font color="Teal">A2</font>)+1+0.25,IF(<font color="Teal">AND(<font color="#FF00FF">OR(<font color="Navy">WEEKDAY(<font color="Blue">A2,2</font>)<>6,WEEKDAY(<font color="Blue">A2,2</font>)<>7</font>),MOD(<font color="Navy">A2,1</font>)<0.25</font>),INT(<font color="#FF00FF">A2</font>)+0.25,A2</font>)</font>)</font>),B2</font>)-1</font>)*(<font color="Blue">"18:00"-"6:00"</font>)+MOD(<font color="Blue">B2,1</font>)-MOD(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">A2,2</font>)=6,INT(<font color="Green">A2</font>)+2+0.25,IF(<font color="Green">OR(<font color="Purple">WEEKDAY(<font color="Teal">A2,2</font>)=7,MOD(<font color="Teal">A2,1</font>)>0.75</font>),INT(<font color="Purple">A2</font>)+1+0.25,IF(<font color="Purple">AND(<font color="Teal">OR(<font color="#FF00FF">WEEKDAY(<font color="Navy">A2,2</font>)<>6,WEEKDAY(<font color="Navy">A2,2</font>)<>7</font>),MOD(<font color="#FF00FF">A2,1</font>)<0.25</font>),INT(<font color="Teal">A2</font>)+0.25,A2</font>)</font>)</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
 

MasterK

New Member
Forgot to account for if it is a work day but before 6AM this should cover that.

ABC
28/17/2019 15:308/19/2019 17:3011:30:00
38/14/2019 18:308/15/2019 17:3011:30:00
48/14/2019 7:008/16/2019 13:0030:00:00
58/22/2019 5:008/22/2019 7:001:00:00

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

Worksheet Formulas
CellFormula
C2=(NETWORKDAYS(IF(WEEKDAY(A2,2)=6,INT(A2)+2+0.25,IF(OR(WEEKDAY(A2,2)=7,MOD(A2,1)>0.75),INT(A2)+1+0.25,IF(AND(OR(WEEKDAY(A2,2)<>6,WEEKDAY(A2,2)<>7),MOD(A2,1)<0.25),INT(A2)+0.25,A2))),B2)-1)*("18:00"-"6:00")+MOD(B2,1)-MOD(IF(WEEKDAY(A2,2)=6,INT(A2)+2+0.25,IF(OR(WEEKDAY(A2,2)=7,MOD(A2,1)>0.75),INT(A2)+1+0.25,IF(AND(OR(WEEKDAY(A2,2)<>6,WEEKDAY(A2,2)<>7),MOD(A2,1)<0.25),INT(A2)+0.25,A2))),1)

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

<tbody>
</tbody>
Thanks again. I didn't notice that before but not it's spot on.
 

MasterK

New Member
I'm back again. I usually know where to add holidays in simpler networkdays strings, but I've tried to dabble here and it's not working. I can either list them out individually or make a sheet with a table. Either way I just need to know where to insert.

Thanks in advance!
 

Scott T

Well-known Member
RED= start date
Blue= end date
F2:F7 = holiday list

Code:
=(NETWORKDAYS([COLOR=#ff0000]IF(WEEKDAY(A2,2)=6,INT(A2)+2+0.25,IF(OR(WEEKDAY(A2,2)=7,MOD(A2,1)>0.75),INT(A2)+1+0.25,IF(AND(OR(WEEKDAY(A2,2)<>6,WEEKDAY(A2,2)<>7),MOD(A2,1)<0.25),INT(A2)+0.25,A2)))[/COLOR],[COLOR=#0000cd]B2,[/COLOR][COLOR=#ffa500]F2:F7[/COLOR])-1)*("18:00"-"6:00")+MOD(B2,1)-MOD(IF(WEEKDAY(A2,2)=6,INT(A2)+2+0.25,IF(OR(WEEKDAY(A2,2)=7,MOD(A2,1)>0.75),INT(A2)+1+0.25,IF(AND(OR(WEEKDAY(A2,2)<>6,WEEKDAY(A2,2)<>7),MOD(A2,1)<0.25),INT(A2)+0.25,A2))),1)
 

MasterK

New Member
RED= start date
Blue= end date
F2:F7 = holiday list

Code:
=(NETWORKDAYS([COLOR=#ff0000]IF(WEEKDAY(A2,2)=6,INT(A2)+2+0.25,IF(OR(WEEKDAY(A2,2)=7,MOD(A2,1)>0.75),INT(A2)+1+0.25,IF(AND(OR(WEEKDAY(A2,2)<>6,WEEKDAY(A2,2)<>7),MOD(A2,1)<0.25),INT(A2)+0.25,A2)))[/COLOR],[COLOR=#0000cd]B2,[/COLOR][COLOR=#ffa500]F2:F7[/COLOR])-1)*("18:00"-"6:00")+MOD(B2,1)-MOD(IF(WEEKDAY(A2,2)=6,INT(A2)+2+0.25,IF(OR(WEEKDAY(A2,2)=7,MOD(A2,1)>0.75),INT(A2)+1+0.25,IF(AND(OR(WEEKDAY(A2,2)<>6,WEEKDAY(A2,2)<>7),MOD(A2,1)<0.25),INT(A2)+0.25,A2))),1)
Lifesaver. I had a manual formula subtracting hours. This will be much easier.
 

Some videos you may like

This Week's Hot Topics

Top