business days between 2 date feilds

[Guest]

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

 

[Biff]

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

 

[Ron Rosenfeld]

=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

 

[Biff]

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

 

[Ron Rosenfeld]

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]

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

 

[Daniel.M]

Biff,

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.

 

[Ron Rosenfeld]

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

 

[Biff]

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

 

[Ron Rosenfeld]

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

 

[Biff]

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