Excel - Date/Time using NETWORKDAYS

[KetanR]

Hi,


Have been working on a project,


Am calculating the total hours worked for business days based on the Start/End date & time excluding holidays and weekends but the result is not as i wanted for,


FYI,


BUSINESS TIME: MON 5:00 AM UNTIL SAT 5:00 AM
FORMULA USED IN CELL (D2): =(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")


If replaced business time with Start/Stop time in the formula,
Result i get is 00:00 or it doesn’t add up the hours from SAT 12 AM - 5AM


Below pasted data from google spreadsheet is 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


Your advice in this regard is highly appreciated.

Thanks in advance.