+ Reply to Thread
Results 1 to 8 of 8

Count Days EXCLUDING ONLY Sundays and a named range for Holidays

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    Hello!

    I'm struggling to find a way to count the number of days between 2 dates excluding only Sundays and a named range for Holidays.

    =Networkdays(B2,A2,Holidays) does not work (as shown) as it also excludes Saturdays. Essentially, this program is to count delivery days for U.S. mail.

    An example spreadsheet is attached which provides a bit more clarity as to what I'm trying to accomplish.ExcelForum - Count_Days.xlsx

    Using Excel 2010.

    Any assistance would be greatly appreciated!

    Thanks so much!!!

    Seaplane Jack

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    How about:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    Or try:
    =NETWORKDAYS(A2,B2,Holidays)+INT((B2-A2-WEEKDAY(B2-6)+8)/7)
    Quang PT

  4. #4
    Registered User
    Join Date
    06-13-2012
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    ExcelForum - Count_Days - Rev1.xlsxI've been playing with both suggestions above and can't get either to work for excluding Sundays. They both work when a holiday is involved, but no matter what I do I can't get them to "account for" Sundays.

    See the revised spreadsheet example attached. Whether using a simple formula (=B2-A2) or one of the suggestions above, both show 3 days when the actual result should be 2 since Jan 05 is a Sunday.

    Am I doing something wrong (wouldn't be the first time)???

    Thanks, again!!!

    Seaplane Jack

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    Netowrkdays is INCLUSIVE, meaning it counts BOTH the start date AND the end date.
    So your dates in A2 and B2 are from Friday to Monday
    Excluding only Sunday, then it counts Friday Saturday and Monday = 3

    To correct for this, either add 1 to the start date, or subract 1 from the end date
    Depending on if you want to exclude the Start or End date..

    Exclude Start Date from the count
    =NETWORKDAYS.INTL(A2+1,B2,11,Holidays)

    Exclude End Date from the count
    =NETWORKDAYS.INTL(A2,B2-1,11,Holidays)

    Hope that helps.
    Last edited by Jonmo1; 01-14-2014 at 04:06 PM.

  6. #6
    Registered User
    Join Date
    06-13-2012
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    Ok...that makes perfect sense and now works perfectly!

    Thank you so very, very much!!!

    Best Regards,

    Seaplane Jack

  7. #7
    Registered User
    Join Date
    06-13-2012
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    Ok...that makes perfect sense and now works perfectly!

    Thank you so very, very much!!!

    Best Regards,

    Seaplane Jack

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Count Days EXCLUDING ONLY Sundays and a named range for Holidays

    You're welcome..

    FYI,

    DON'T do it this way
    =NETWORKDAYS.INTL(A2,B2,11,Holidays)-1
    That could have inaccurate results..

    Always Add to the start date or Subtract from the End date.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excluding holidays and sundays
    By balundl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-25-2013, 12:38 PM
  2. [SOLVED] Calculating Business Days by excluding Saturdays/Sundays and other Public Holidays
    By all4excel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-03-2008, 10:15 PM
  3. trying to calculate regular days in date range excluding holidays
    By DKY in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2008, 09:12 PM
  4. counting days excluding saturdays and sundays
    By p_dhoke in forum Excel General
    Replies: 2
    Last Post: 04-24-2007, 04:34 AM
  5. Replies: 2
    Last Post: 04-08-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1