Calculate minutes between dates based on 9hr work days
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 -- JQS |
Calculate minutes between dates based on 9hr work days
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 -- __________________________________ HTH Bob "JQS" wrote in message ... 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 -- JQS |
All times are GMT +1. The time now is 11:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com