Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
abs2299
 
Posts: n/a
Default 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.
  #2   Report Post  
CLR
 
Posts: n/a
Default

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.



  #3   Report Post  
Jack
 
Posts: n/a
Default

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.

  #4   Report Post  
tjtjjtjt
 
Posts: n/a
Default

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.

  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

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





  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #7   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #8   Report Post  
CLR
 
Posts: n/a
Default

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



  #9   Report Post  
Myrna Larson
 
Posts: n/a
Default

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there a way to calculate business working days between dates i. hjyoungii Excel Worksheet Functions 2 February 23rd 05 05:25 PM
how do I calculate the days between dates? stucklady! Excel Discussion (Misc queries) 7 February 12th 05 05:39 PM
HOW TO CALCULATE THE DAYS? Bel Excel Worksheet Functions 2 February 2nd 05 08:05 PM
How do I calculate difference in days & hours between two dates e. probi2 Excel Worksheet Functions 1 January 12th 05 04:59 PM
Subracting Dates to come up with the # of days between them KimberlyC Excel Worksheet Functions 8 December 20th 04 10:46 PM


All times are GMT +1. The time now is 03:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"