help with subtracting time excluding weekends and non-working hour

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jon Ratzel - 02 Feb 2008 22:55 GMT

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

Reply to this Message

Bob Phillips - 02 Feb 2008 23:13 GMT

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

Signature

---
HTH

Bob

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

> I'm trying to subtract to sets of dates to figure out the time between but
> I
[quoted text clipped - 28 lines]
>
> Jon

Reply to this Message

Jon Ratzel - 03 Feb 2008 04:31 GMT

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
[quoted text clipped - 40 lines]
> >
> > Jon

Reply to this Message