business days between 2 date feilds

if I have a date in A1 and a Date in B1.....is there a function or formula
for c1 that will tell me the number of business days between the two dates?
thanks

A1 01/05/2005
B1 01/17/2007
C1 ????

=NETWORKDAYS(A1,B1,0)

This returns 531 for your example

Note:
Saturdays and sundays are considered holidays
0 is the number of holidays, other than sat and sun

- Mangesh

Hi!

Business days = Mon thru FRI excluding holidays

Try one of these:

This first one requires that the Analysis ToolPak add-in
be installed:

=NETWORKDAYS(A1,B1,F1:F4)

F1:F4 is a list of holidays.

This one does not require the ATP:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))-
COUNT(F1:F4)

Format cell as GENERAL.

Biff

>-----Original Message-----
>if I have a date in A1 and a Date in B1.....is there a
function or formula
>for c1 that will tell me the number of business days
between the two dates?
>thanks
>
>A1 01/05/2005
>B1 01/17/2007
>C1 ????
>.
>

On Thu, 10 Mar 2005 23:11:59 -0800, "Biff" <[email protected]> wrote:

>=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))-
>COUNT(F1:F4)

Did you test this if
1. A holiday date is outside of the range A1:B1?
2. A holiday date falls on a Saturday or Sunday?


--ron

Hi Ron!

>Did you test this if
> 1. A holiday date is outside of the range A1:B1?
> 2. A holiday date falls on a Saturday or Sunday?

Why would someone list a holiday date outside the range?

I would assume that people only list those holidays that
would fall on a business day. I'll bet most people have to
look at a calander to see when the holidays occur.

Biff

>-----Original Message-----
>On Thu, 10 Mar 2005 23:11:59 -0800, "Biff"
<[email protected]> wrote:
>
>>=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))-
>>COUNT(F1:F4)
>
>Did you test this if
> 1. A holiday date is outside of the range A1:B1?
> 2. A holiday date falls on a Saturday or Sunday?
>
>
>--ron
>.
>

On Fri, 11 Mar 2005 10:05:30 -0800, "Biff" <[email protected]> wrote:

>Why would someone list a holiday date outside the range?

They might have a list of holidays for the year, or for several years, but only
be interested in different date ranges.

>
>I would assume that people only list those holidays that
>would fall on a business day. I'll bet most people have to
>look at a calander to see when the holidays occur.


I don't specifically object to your approach. However, in my opinion, you
should explicitly state the assumptions and limitations of your approach.

The NetWorkDays function, which you are attempting to mimic, does not have
either of these limitations.


--ron

Hi Ron!

>The NetWorkDays function, which you are attempting to
>mimic, does not have either of these limitations.

Are you sure about that?

Assume you get Christmas Eve and Christmas Day off but for
the time period in question both of these dates are on a
weekend. When this happens, the company you work for gives
the employees off 12/22 and 12/23 which are business days.

How does NETWORKDAYS account for that?

I do see your point and it's well taken but this is one of
those situations that you cannot completely automate, IMO.

Biff

>-----Original Message-----
>On Fri, 11 Mar 2005 10:05:30 -0800, "Biff"
<[email protected]> wrote:
>
>>Why would someone list a holiday date outside the range?
>
>They might have a list of holidays for the year, or for
several years, but only
>be interested in different date ranges.
>
>>
>>I would assume that people only list those holidays that
>>would fall on a business day. I'll bet most people have
to
>>look at a calander to see when the holidays occur.
>
>
>I don't specifically object to your approach. However,
in my opinion, you
>should explicitly state the assumptions and limitations
of your approach.
>
>The NetWorkDays function, which you are attempting to
mimic, does not have
>either of these limitations.
>
>
>--ron
>.
>