Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Time elapsed | Excel Worksheet Functions | |||
Calculating Elasped Time | Excel Discussion (Misc queries) | |||
With this business with ONLY 5,- EUR OVER 61,370 EUR in 3 months earn!! | Charts and Charting in Excel | |||
With this business with ONLY 5,- EUR OVER 61,370 EUR in 3 months earn!! | Charts and Charting in Excel | |||
Time Sheets | Excel Discussion (Misc queries) |