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

Start Date	End Date	Elapsed Time	Comment	Correct Value
8/17/19 15:30 (Sun) <tbody> </tbody>	8/19/19 17:30 (Mon) <tbody> </tbody>	2:00	Incorrect value	11:30 hrs
8/14/19 18:30 (Wed) <tbody> </tbody>	8/15/19 17:30 (Thu) <tbody> </tbody>	11:00	Incorrect Value	11:30 hrs
8/14/19 7:00 (Wed) <tbody> </tbody>	8/16/19 13:00 (Fri) <tbody> </tbody>	30:00	Correct	30:00 hrs

<tbody>
</tbody>

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

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

Sheet1​

Worksheet Formulas Cell 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

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

Sheet1​

Worksheet Formulas Cell 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.