Time and Business Hours
I'm having a hard time getting a function to calculate number of minutes
between two times that takes into account business hours and business days. Has anyone done this before? |
Time and Business Hours
Hi!
Assumptions: Business days are Mon thru Fri excluding holidays. Business hours are 8:00 AM to 6:00 PM Dates/times are entered in the same cell: A1 = 10/1/2004 2:25 PM A2 = 10/14/2004 5:02 PM If you want to exclude holidays you need to make a list of those dates and then refer to that list in the NETWORKDAYS function as the 3rd argument. =(IF(NETWORKDAYS(A1,A1)=1,18/24-MOD(A1,1),0) +IF(NETWORKDAYS(A2,A2)=1,MOD(A2,1)-8/24,0)+NETWORKDAYS(A1+1,A2-1)*10/24)*1440 Format the cell as GENERAL In the formula: 18/24 refers to 6:00 PM (end of business hours) 8/24 refers to 8:00 AM (start of business hours) 10/24 refers to the total hours that comprise the business day (8:00 AM to 6:00 PM) This requires that the Analysis ToolPak add-in be installed. Based on the above formula using those date/times (not using the holidays argument), the result is 6157 minutes. Biff "Brett" wrote in message ... I'm having a hard time getting a function to calculate number of minutes between two times that takes into account business hours and business days. Has anyone done this before? |
All times are GMT +1. The time now is 03:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com