Calculating elapsed hours with weekends.

[MasterK]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Elapsed Time[/TD]
[TD]Comment[/TD]
[TD]Correct Value[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR="class: grid"]
[TD="width: 110"]8/17/19 15:30 (Sun)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR="class: grid"]
[TD="width: 101"]8/19/19 17:30 (Mon)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2:00[/TD]
[TD]Incorrect value[/TD]
[TD]11:30 hrs[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR="class: grid"]
[TD="width: 110"]8/14/19 18:30 (Wed)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR="class: grid"]
[TD="width: 101"]8/15/19 17:30 (Thu)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11:00[/TD]
[TD]Incorrect Value[/TD]
[TD]11:30 hrs[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR="class: grid"]
[TD="width: 110"]8/14/19 7:00 (Wed)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR="class: grid"]
[TD="width: 101"]8/16/19 13:00 (Fri)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]30:00[/TD]
[TD]Correct[/TD]
[TD]30:00 hrs[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks much for your assistance in advance.

 

[James006]

Hello,

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

 

[MasterK]

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]

Try

Book1
	B	C	D
3	8/17/2019 15:30	8/19/2019 17:30	11:30:00
4	8/14/2019 18:30	8/15/2019 17:30	11:30:00
5	8/14/2019 7:00	8/16/2019 13:00	30:00:00
Sheet1

Cell Formulas
Range	Formula
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)

 

[MasterK]

Try

	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]8/17/2019 15:30[/TD]
[TD="align: right"]8/19/2019 17:30[/TD]
[TD="align: right"]11:30:00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8/14/2019 18:30[/TD]
[TD="align: right"]8/15/2019 17:30[/TD]
[TD="align: right"]11:30:00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8/14/2019 7:00[/TD]
[TD="align: right"]8/16/2019 13:00[/TD]
[TD="align: right"]30:00:00[/TD]

</tbody>

Sheet1​

[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] "]D3[/TH]
[TD="align: left"]=(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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Amazing, thank you !

 

[Scott T]

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

Book1
	A	B	C
2	8/17/2019 15:30	8/19/2019 17:30	11:30:00
3	8/14/2019 18:30	8/15/2019 17:30	11:30:00
4	8/14/2019 7:00	8/16/2019 13:00	30:00:00
5	8/22/2019 5:00	8/22/2019 7:00	1:00:00
Sheet1

Cell Formulas
Range	Formula
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)

 

[MasterK]

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

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]8/17/2019 15:30[/TD]
[TD="align: right"]8/19/2019 17:30[/TD]
[TD="align: right"]11:30:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8/14/2019 18:30[/TD]
[TD="align: right"]8/15/2019 17:30[/TD]
[TD="align: right"]11:30:00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8/14/2019 7:00[/TD]
[TD="align: right"]8/16/2019 13:00[/TD]
[TD="align: right"]30:00:00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8/22/2019 5:00[/TD]
[TD="align: right"]8/22/2019 7:00[/TD]
[TD="align: right"]1:00:00[/TD]

</tbody>

Sheet1​

[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] "]C2[/TH]
[TD="align: left"]=(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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[MasterK]

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]

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

=(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]

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

=(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.