Time and Business Hours

[Brett]

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?

[Biff]

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" <Brett@discussions.microsoft.com> wrote in message
news:812611F3-7D8B-4C7F-8B3F-90ADB66834A4@microsoft.com...
> 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?