Calculate minutes between dates based on 9hr work days

J

JQS

Need to calculate "business minutes" between dates.
ie...08/29/08 4:00p to 9/2/08 9:00a result in 120 minutes (due to 8a-5p
work day, less weekends and holidays). Can you help? thank you
 
B

Bob Phillips

If you want it in minutes

=((NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<6)-(WEEKDAY(A2,2)<6))*9
+((WEEKDAY(A1,2)<6)*(MIN(MAX(0,TIME(17,0,0)-MOD(A1,1)),TIME(8,0,0)))
+(WEEKDAY(A2,2)<6)*(MIN(MAX(MOD(A2,1)-TIME(8,0,0),0),TIME(8,0,0))))*24
-SUMPRODUCT(--(WEEKDAY(holidays,2)<5),--(ISNUMBER(MATCH(holidays,ROW(INDIRECT(INT(A1)+1&":"&INT(A2)-1)),0))))*9)*60
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top