formula to calculate # of days between dates, excluding holidays
 

I am wondering if there is a formula out there that will calculate the number
of days between two dates, but exlclude holidays. I know there is a formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.

Assuming your dates are in A1 and B1,
Create a RangeName called Holidays in an out of the way place and list your
holidays there........then use

=(B1-A1)-COUNT(Holidays)

Vaya con Dios,
Chuck, CABGx3



"abs2299" wrote in message
...
I am wondering if there is a formula out there that will calculate the
number
of days between two dates, but exlclude holidays. I know there is a
formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.

there is no formula that will exclude holidays, the Days360 function will
calculate the number of days between 2 dates. Example:

=Days360("1/1/2005","1/15/2005") = 14

-Jack



"abs2299" wrote:

I am wondering if there is a formula out there that will calculate the number
of days between two dates, but exlclude holidays. I know there is a formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.

If you install the Analysis Toolpak from Tools | Addins, you can use
=Networkdays(start_date,end_date,holiday_list)

tj

"abs2299" wrote:

I am wondering if there is a formula out there that will calculate the number
of days between two dates, but exlclude holidays. I know there is a formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.

=(SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+
1-{1;2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))-SUMPRODUCT(ISNUMBER(MATCH
(WEEKDAY(holidays),{1;2;3;4;5;6;7},0))*(holidays= MIN(end_date,start_date))*
(holidays<=MAX(end_date,start_date))))*-(start_dateend_date)

start_date anmd end_date are two date cvells, holidays is a holiday list
range name
--

HTH

RP
(remove nothere from the email address if mailing direct)


"abs2299" wrote in message
...
I am wondering if there is a formula out there that will calculate the
number
of days between two dates, but exlclude holidays. I know there is a
formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.

On Wed, 2 Mar 2005 14:25:05 -0800, "abs2299"
wrote:

I am wondering if there is a formula out there that will calculate the number
of days between two dates, but exlclude holidays. I know there is a formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.


=EndDate-StartDate-COUNTIF(Holidays,"="&StartDate)
+COUNTIF(Holidays,""&EndDate)

Holidays is a named range that has your list of holiday dates.


--ron

On Wed, 2 Mar 2005 17:50:55 -0500, "CLR" wrote:

Assuming your dates are in A1 and B1,
Create a RangeName called Holidays in an out of the way place and list your
holidays there........then use

=(B1-A1)-COUNT(Holidays)

Vaya con Dios,
Chuck, CABGx3



That will only work if the list of holidays only includes holidays between the
dates in A1 and B1


--ron

I stand corrected..........Thank you Sir.

Vaya con Dios,
Chuck, CABGx3


"Ron Rosenfeld" wrote in message
...
On Wed, 2 Mar 2005 17:50:55 -0500, "CLR" wrote:

Assuming your dates are in A1 and B1,
Create a RangeName called Holidays in an out of the way place and list
your
holidays there........then use

=(B1-A1)-COUNT(Holidays)

Vaya con Dios,
Chuck, CABGx3



That will only work if the list of holidays only includes holidays between
the
dates in A1 and B1


--ron

PLEASE..... don't use DAYS360. It's intended for financial calculations. It
will, for example, tell you that the number of days between 2/1/2005 and
3/1/2005 is 30 instead of 28.

I think this formula will work. Start date is in A1, end date is in B1, and
named range Holidays has the list of holidays to be removed.

=B1-A1-(COUNTIF(Holidays,"="&A1)-COUNTIF(Holidays,""&B1))




On Wed, 2 Mar 2005 14:53:03 -0800, "Jack"
wrote:

there is no formula that will exclude holidays, the Days360 function will
calculate the number of days between 2 dates. Example:

=Days360("1/1/2005","1/15/2005") = 14

-Jack



"abs2299" wrote:

I am wondering if there is a formula out there that will calculate the
number
of days between two dates, but exlclude holidays. I know there is a
formula
that will calculate work days, but I need one to calculate all days of the
week, not just work days.