Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Diff. between 2 dates, NOT including certain days

    (Excel 2000) (In the example below, the dates used are in MM/DD/YY format). In column A, I have certain dates and in column B also, I have dates as well (and these are later in time than those in col A). In col C, I calculate the difference by subtracting the value in col A from that in col B. For example, I may have 3/13/01 in A1, 3/20/01 in B1 and C1 will calculate the difference as 7. But the problem is: I DON'T want to include Saturdays & Sundays, only the weekdays! So in reality, the true difference between the 2 dates in the example above is 5 (i.e. 5 business days). The current formula I am using is (B1-A1); is there a way to modify it so that Saturdays & Sundays are not included in the difference?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Diff. between 2 dates, NOT including certain days

    What you are looking for is the Networkdays() function. It is part of Excel's Data Analysis Tool Pak. This is not installed as part of a normal Excel installation. Check Excel help and it will tell you how to install it.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    Actually, I do have Networkdays installed and its displaying the results without including the weekends. That's good! To exclude holidays, I understand that I either have to input those dates or refer it to a cell range which has those dates; problem is, I don't have the dates preprogrammed and it would be tedious to create such a list ("lazy bum" - that's me). Is there an *easy* way to get the list of holidays (e.g. import it from somewhere on the internet), place it somewhere on the sheet, and give it a name (e.g. "Holidays not on weekends - 2001")?

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    I don't know of any ready made lists. I could send you the one I use. It only goes back to 1996.

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    Actually, I would need it for this year and future years. And I forgot to add - the list should be for holidays in the USA that don't fall on weekends.

  6. #6
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    For a list of holidays, take a look at Outlook.Txt or .HOL.
    It's the file that Outlook uses to populate its calendar.
    You'll have to do some editing, but it's a start.
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    I located the outlook.txt file and opened it. I notice a few problems right away:
    1) The holidays are upto 2002 only;
    2) There appear to be many holidays that are not applicable to the USA; some I don't even recognize! (but I wouldn't mind having them as holidays <grin>!)
    3) The list is very long and you are right it would need a lot of editing.
    4) There is no mention if the holiday falls on a weekend - I guess one would have to check it manually.
    Is there an easier (lazier) way?

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    For anyone who may be following this thread, I found a list of US Federal holidays at
    http://www.holidayfestival.com/USA.html.
    It appears to be manageable so perhaps with a little work the values can be input into Excel.

  9. #9
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    For Outlook, the countries are identified between [ ]'s.
    Other sites are:
    and <A target="_blank" HREF=http://www.earthcalendar.net/countries/2001/USA.html>Why even go to work?, also
    <A target="_blank" HREF=http://www.cpearson.com/excel/holidays.htm>Chip Pearson has some VBA stuff here</A>.
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  10. #10
    Star Lounger
    Join Date
    Feb 2003
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    Thanks - those links were helpful.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: And Holidays?

    Use the Weekday function to determine if the date falls on a weekend or not (1 and 7 are Sunday and Saturday). That way, import the list of holidays indiscriminately, then let the formula determine which ones are weekends (or weekdays). Filter the list and delete, or have your other cells decide based on what the Weekday result is.

    This is my suggestion for a lazy way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •