I have been using the formula below to calculate the total hours run time within a certain time range (12:00PM - 8:00PM) for each of our units. This calculates only the hours run within the time range whether the unit ran for one hour or multiple days. The issue I need help with is that the formula only calculates workdays (NETWORKDAYS) and does not include weekends. We are now running our units seven days a week and I need assistance changing the formula to calculate seven days a week. I have been struggling with this for a couple of days now.
A2= START TIME
B2= END TIME
A5= START DATE
B5= END DATE
=IF(OR($B$2 < $A$2,B5 < A5),0,(NETWORKDAYS(A5,B5)-(NETWORKDAYS(A5,A5)*IF(MOD(A5,1) > $B$2,1,(MAX($A$2,MOD(A5,1))-$A$2)/($B$2-$A$2)))-(NETWORKDAYS(B5,B5)*IF(MOD(B5,1) < $A$2,1,($B$2-MIN($B$2,MOD(B5,1)))/($B$2-$A$2))))*($B$2-$A$2)*24)
Below is an example. The unit ran from Friday at noon until Monday at 9:00PM. The total hours calculated within the time range (12:00pm - 8:00PM) should be 32 but since the formula is not capturing weekend runs I receive a total of 16.
START TIME
12:00 PM
END TIME
8:00 PM
START DATE
FRI 11/3/17 12:00 PM
END DATE
MON 11/6/17 9:00PM
NET WORK HOURS (DECIMAL)
16.00
NET WORK HOURS (H:MM)
16:00
Bookmarks