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