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,
<tbody>
</tbody>
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,
| 1 | A | B | C | D |
| 2 | HOLIDAYS | START DATE | END DATE | RESULT |
| 3 | 8/15/2017 | 8/3/2017 7:56:00 | 9/2/2017 12:03:00 | 232:03:00 |
| 4 | 8/25/2017 | |||
| 5 | 8/26/2017 | |||
| 6 | 8/27/2017 | |||
| 7 | 8/28/2017 | |||
| 8 | 8/29/2017 | |||
| 9 | 8/30/2017 | |||
| 10 | 8/31/2017 |
<tbody>
</tbody>
Last edited:
