Hi, all:
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.
<tbody>
</tbody>
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 | END TIME | ||
12:00 PM | 8:00 PM | ||
START DATE | END DATE | NET WORK HOURS (DECIMAL) | NET WORK HOURS (H:MM) |
FRI 11/3/17 12:00 PM | MON 11/6/17 9:00PM | 16.00 | 16:00 |
<tbody>
</tbody>