Removing non Work Hours from Excel calculation

[lindaho98]

....BUT, if I keep the time at (5,30,0) then the times are correct.Must
be something else in the formula you wrote that needs to be changed.
Thanks again !

 

[Bob Phillips]

Nope!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Revise that, yep!

=(NETWORKDAYS(INT(A1),INT(B1))-(WEEKDAY(A1,2)<6)-(WEEKDAY(B1,2)<6))*12.5/24
+(MAX(MOD(A1,1),TIME(19,0,0))-MAX(MOD(A1,1),TIME(6,30,0)))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))-MIN(MOD(B1,1),TIME(6,30,0)))*(WEEKDAY(B1,2)<6)

It might be best to put the start and end times in cells, say E1 and F1 and
use that. makes it easier to change

=(NETWORKDAYS(INT(A1),INT(B1))-(WEEKDAY(A1,2)<6)-(WEEKDAY(B1,2)<6))*(f1-e1)
+(MAX(MOD(A1,1),F1)-MAX(MOD(A1,1),E1))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),F1)-MIN(MOD(B1,1),E1))*(WEEKDAY(B1,2)<6)



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[daddylonglegs]

Hi Linda,

I see Bob's done all the work here but here's a slightly different
version that you could use....

=(NETWORKDAYS(A1,B1)-1)*(F1-E1)+IF(WEEKDAY(B1,2)>5,F1,MEDIAN(MOD(B1,1),F1,E1))-IF(WEEKDAY(A1,2)>5,E1,MEDIAN(MOD(A1,1),F1,E1))