I'm trying to calculate the number of minutes between two dates and times but
I want the calculation to consider only the work day of 8:30AM to 5:00PM. So
if cell A1 has 12/13/07 4:59 PM and cell B1 has 12/14/07 8:30 AM, I need cell
C1 to calculate an answer of 00:01:00 (when formatted as HH:MM:SEC) and not
the 15:31:00 that I would get if I simply subtracted B1 from A1. The tricky
part is that I have weekends that I need to account for as well. Any
suggestions?

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

--
---
HTH

Bob


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



"Jon Ratzel" <Jon wrote in message
...
I'm trying to calculate the number of minutes between two dates and times
but
I want the calculation to consider only the work day of 8:30AM to 5:00PM.
So
if cell A1 has 12/13/07 4:59 PM and cell B1 has 12/14/07 8:30 AM, I need
cell
C1 to calculate an answer of 00:01:00 (when formatted as HH:MM:SEC) and
not
the 15:31:00 that I would get if I simply subtracted B1 from A1. The
tricky
part is that I have weekends that I need to account for as well. Any
suggestions?

Thanks for the fast response - it works great!

Jon




"Bob Phillips" wrote:

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

--
---
HTH

Bob


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



"Jon Ratzel" <Jon wrote in message
...
I'm trying to calculate the number of minutes between two dates and times
but
I want the calculation to consider only the work day of 8:30AM to 5:00PM.
So
if cell A1 has 12/13/07 4:59 PM and cell B1 has 12/14/07 8:30 AM, I need
cell
C1 to calculate an answer of 00:01:00 (when formatted as HH:MM:SEC) and
not
the 15:31:00 that I would get if I simply subtracted B1 from A1. The
tricky
part is that I have weekends that I need to account for as well. Any
suggestions?