help with subtracting time excluding weekends and non-working hour

[Jon Ratzel]

I'm trying to subtract to sets of dates to figure out the time between but I
need my formula to exclude all weekends and any hours before 8:30AM and after
5:00PM. I have a formula that seems to work most of the time, but gives me an
incorrect result sometimes. Here's what I'm using:

=NETWORKDAYS(a2,b2)-2+((WEEKDAY(a2,2)<6)*(MAX(0,TIME(17,0,0)-MOD(a2,1))))
-((WEEKDAY(a2,2)<6)*(MAX(0,TIME(8,30,0)-MOD(a2,1))))+((WEEKDAY(b2,2)<6)
*(MAX(0,MOD(b2,1)-TIME(8,30,0))))-((WEEKDAY(b2,2)<6)*(MAX(0,MOD(b2,1)
-TIME(17,0,0))))

Here's what it's giving me as an example:
correct result:
Start Time: 1/18/08 4:59 PM
End Time: 1/21/08 8:30 AM
Correct Result: 0:01:00 (HH:MM:SS format, 1/18 is a Fri so there's been only
1 minute of work time elapsing between the two times)

Incorrect result:
Start Time: 1/15/08 4:59 PM
End Time: 1/21/08 8:30 AM
Incorrect Result: 72:01:00 (I should see 25:31:00 for work on 1/16, 1/17,
and 1/18 plus one minute on 1/15.

The formula seems to work in hundreds of rows of my data but misses others.
Anyone know why? Thanks for all the help!

Jon

 

[Bob Phillips]

I think the reason is that it is calculating whole days as 24 hours, whereas
you seem to be saying that it should be 8.5 hours. If that is the case, how
can hundreds of your cases work, it only works where you have no whole days
in between (and I am not referring here to days that end after 5PM say, but
whole working days such as Mon-Wed).

If my reasoniung is correct, try

=(NETWORKDAYS(A4,B4)-2)*8.5/24+((WEEKDAY(A4,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A4,1))))
-((WEEKDAY(A4,2)<6)*(MAX(0,TIME(8,30,0)-MOD(A4,1))))+((WEEKDAY(B4,2)<6)
*(MAX(0,MOD(B4,1)-TIME(8,30,0))))-((WEEKDAY(B4,2)<6)*(MAX(0,MOD(B4,1)-TIME(17,0,0))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jon Ratzel]

This seems to work! Thank you so much!

I think the reason is that it is calculating whole days as 24 hours, whereas
you seem to be saying that it should be 8.5 hours. If that is the case, how
can hundreds of your cases work, it only works where you have no whole days
in between (and I am not referring here to days that end after 5PM say, but
whole working days such as Mon-Wed).

If my reasoniung is correct, try

=(NETWORKDAYS(A4,B4)-2)*8.5/24+((WEEKDAY(A4,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A4,1))))
-((WEEKDAY(A4,2)<6)*(MAX(0,TIME(8,30,0)-MOD(A4,1))))+((WEEKDAY(B4,2)<6)
*(MAX(0,MOD(B4,1)-TIME(8,30,0))))-((WEEKDAY(B4,2)<6)*(MAX(0,MOD(B4,1)-TIME(17,0,0))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)