Help removing NETWORKDAYS function from formula

[kern630]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]START TIME[/TD]
[TD]END TIME[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]NET WORK HOURS (DECIMAL)[/TD]
[TD]NET WORK HOURS (H:MM)[/TD]
[/TR]
[TR]
[TD]FRI 11/3/17 12:00 PM[/TD]
[TD]MON 11/6/17 9:00PM[/TD]
[TD]16.00[/TD]
[TD]16:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rorylfc]

I haven't ran through your formula fully, but at quick glance, it looks like a comma is missing from you first OR between B5$B$2?!?

=IF(OR($B$2<$A$2,B5<a5),0,(networkdays(a5,b5)-(networkdays(a5,a5)*if(mod(a5,1)>$B$2,1,(</a5),0,(networkdays(a5,b5)-(networkdays(a5,a5)*if(mod(a5,1)>

 

[kern630]

Good catch. I must have fat fingered the paste function and posted incorrectly. Here is the correct formula I am currently using.

=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)

 

[kern630]

Geez. I did it again. THIS is the actual formula. Sorry about that

=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)

 

[Rorylfc]

Looks like you have pasted it incorrectly again!!!

 

[kern630]

I'm doing something wrong. When I submit my thread the formula changes.


=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)

 

[kern630]

https://drive.google.com/file/d/0B_MFKQOHDT7hUFFwTTJaNzRuVTg/view?usp=sharing

 

[RoryA]

When posting formulas that include < and > signs, it is safest to either put a space after them (especially with < signs) so that the board software doesn't think they are the start of an HTML tag. You can also click the Go Advanced button and turn HTML parsing off before posting.

I've edited your penultimate post to correct the formula display for you.

 

[kern630]

Thank you Rory!

 

[barry houdini]

Hello kern360, did you work out how to do this?

To calculate the total hours based on a 7 day week you can use this formula:

=IF(OR($B$2< $A$2,B5< A5),0,24*((INT(B5)-INT(A5))*($B$2-$A$2)+MEDIAN($A$2,$B$2,MOD(B5,1))-MEDIAN($A$2,$B$2,MOD(A5,1))))