Hi All,
Me and my friend were working on an idea where we could pull total business hours worked excluding holidays and weekends so we started googling and got lucky with this formula =(NETWORKDAYS.INTL(B2,C2,11,A2:A100)-1)*("START TIME"-"END TIME")+IF(NETWORKDAYS.INTL(B2,C2,11,A$2:A$100),MEDIAN(MOD(C2,1),"START TIME","END TIME"),"END TIME")-MEDIAN(NETWORKDAYS.INTL(B2,B2,11,A$2:A$100)*MOD(B2,1),"START TIME","END TIME")
which really worked for us but the problem now is, the formula pulls data only till Friday midnight 12:00 AM (doesn’t add up the hours from SAT 12 AM - 5AM) also If replaced business time with Start/Stop time to the formula(5:00AM-5:00AM), result we get is 00:00.
Is there any way we could get the as required?
Anyone who could advise us in this regard is highly appreciated.
Below table is the sample data for your reference,
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HOLIDAYS[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/15/2017[/TD]
[TD]8/3/2017 7:56:00[/TD]
[TD]9/2/2017 12:03:00[/TD]
[TD]232:03:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8/25/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8/26/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8/27/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8/28/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8/29/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8/30/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8/31/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Me and my friend were working on an idea where we could pull total business hours worked excluding holidays and weekends so we started googling and got lucky with this formula =(NETWORKDAYS.INTL(B2,C2,11,A2:A100)-1)*("START TIME"-"END TIME")+IF(NETWORKDAYS.INTL(B2,C2,11,A$2:A$100),MEDIAN(MOD(C2,1),"START TIME","END TIME"),"END TIME")-MEDIAN(NETWORKDAYS.INTL(B2,B2,11,A$2:A$100)*MOD(B2,1),"START TIME","END TIME")
which really worked for us but the problem now is, the formula pulls data only till Friday midnight 12:00 AM (doesn’t add up the hours from SAT 12 AM - 5AM) also If replaced business time with Start/Stop time to the formula(5:00AM-5:00AM), result we get is 00:00.
Is there any way we could get the as required?
Anyone who could advise us in this regard is highly appreciated.
Below table is the sample data for your reference,
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HOLIDAYS[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/15/2017[/TD]
[TD]8/3/2017 7:56:00[/TD]
[TD]9/2/2017 12:03:00[/TD]
[TD]232:03:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8/25/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8/26/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8/27/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8/28/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8/29/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8/30/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8/31/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Last edited: