formula to calculate # of days between dates, excluding holidays

[abs2299]

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.

 

[CLR]

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

 

[Jack]

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

 

[tjtjjtjt]

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

tj

 

[Bob Phillips]

=(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_date>end_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)

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[Myrna Larson]

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