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
>.
>

Biff,

> >The NetWorkDays function, which you are attempting to
> >mimic, does not have either of these limitations.
>
> Are you sure about that?

The limitations you're refering to are not related to the NetWorkDays function
per se.
They are related to the capacity to specify (via formulas or hard code) REAL
holidays date for a given country, (depending on laws, etc) and to subsequently
put those REAL dates in your holidays range.

There's a difference between an incorrect range of holidays and a flawed
formula.

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

So, to take your example, a formula (included in your holidays range) could
account for Christmas Eve and Christmas Day off occuring on weekend for that
perticular year and produce the proper REAL holiday dates in that year (either
before or after weekend).

Build the proper REAL holidays dates (depending on your country's laws,
traditions) either by hard input or formula, and the NETWORKDAYS() will work
appropriately.

Regards,

Daniel M.

On Fri, 11 Mar 2005 14:00:27 -0800, "Biff" <[email protected]> wrote:

>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.

If 12/22 and 12/23 are holidays, then they should be listed in your holiday
range.


--ron

>If 12/22 and 12/23 are holidays, then they should be
>listed in your holiday range.

Which is exactly the point I was making when I said:

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 Fri, 11 Mar 2005 14:00:27 -0800, "Biff"
<[email protected]> wrote:
>
>>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.
>
>If 12/22 and 12/23 are holidays, then they should be
listed in your holiday
>range.
>
>
>--ron
>.
>

On Fri, 11 Mar 2005 18:04:54 -0800, "Biff" <[email protected]> wrote:

>Which is exactly the point I was making when I said:
>
>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.

But the point that you are missing is that a person would likely list all the
holidays for a time frame -- let us say a year. And then might want to know
the number of workdays in a subset of that time frame -- let us say a month, or
a quarter.

Your formula would give an incorrect answer; especially since you do not
document the fact that in order to get an accurate answer, you must first
decide on the time frame for checking the number of workdays.

NETWORKDAYS does not have this limitation.

Under your formula, you would have to have separate lists of holidays to refer
to depending on the time frame for which you wanted to check the workdays. And
if you changed that time frame, you would have to change the holiday list.
While certainly doable, it just seems a lot more cumbersome than an approach
which allows you to set up a single list of holidays that encompasses all of
the workday time frames in which you might be interested.


--ron

Hi Ron!

>it just seems a lot more cumbersome

Oh, I totally agree with that. But as you know, some users
don't have the ATP and a lot of users try to avoid using
functions in the ATP. Why that is, I certainly don't know.

Biff

>-----Original Message-----
>On Fri, 11 Mar 2005 18:04:54 -0800, "Biff"
<[email protected]> wrote:
>
>>Which is exactly the point I was making when I said:
>>
>>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.
>
>But the point that you are missing is that a person would
likely list all the
>holidays for a time frame -- let us say a year. And then
might want to know
>the number of workdays in a subset of that time frame --
let us say a month, or
>a quarter.
>
>Your formula would give an incorrect answer; especially
since you do not
>document the fact that in order to get an accurate
answer, you must first
>decide on the time frame for checking the number of
workdays.
>
>NETWORKDAYS does not have this limitation.
>
>Under your formula, you would have to have separate lists
of holidays to refer
>to depending on the time frame for which you wanted to
check the workdays. And
>if you changed that time frame, you would have to change
the holiday list.
>While certainly doable, it just seems a lot more
cumbersome than an approach
>which allows you to set up a single list of holidays that
encompasses all of
>the workday time frames in which you might be interested.
>
>
>--ron
>.
>