+ Reply to Thread
Results 1 to 9 of 9

Counting Weekdays + Saturday, omit holidays

  1. #1
    Registered User
    Join Date
    07-30-2007
    Posts
    14

    Counting Weekdays + Saturday, omit holidays

    First off, let me admit my knowledge of worksheet functions extends to the help file. When I need something more complex, I look on the internet and find a formula that does what I need and try to learn something new from it. However, every now and then, I find a formula that uses functions in a way I don't fully understand. I approach the knowledge base of this forum with my question because, frankly, I can't figure this one out...

    Originally I needed a formula that would count the number of days that have passed in a given month, sans Saturday/Sunday and company holidays. The following formula does this quite nicely:
    Please Login or Register  to view this content.
    Where C1 is a date that can be manually input and the range of A31:A41 is where I can input holidays to omit from the count.

    I need a formula (either a modification of this or something new) that will allow the same functionality, but also count Saturday as a work day.

    Also, if it isn't too much trouble, some kind of explanation as to what exactly the formula I posted does to determine which days are weekdays would be VERY much appreciated. Like I said, I try to learn from these formulas I find and I can't recall where I found this one to ask the author.

    --edit--

    Whatever workaround is presented, it can't use the NETWORKDAYS function. I have been unable to get the analysis pack to work on my computer, let alone getting it to work on the computers of the people I send this spreadsheet to.
    Last edited by bryceowen; 07-30-2007 at 10:56 AM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Basically this formula says, look at the date in C1 then take away the date at the beginning of that month to calculate the number of days from the 1st of the month.
    Then omit any days within that period greater than weekday 5 (Friday), so omit all Saturdays and Sundays that fall within that period.
    Then also look at the holiday dates and if any fall within this period, also omit those.

    So all you need to do to this formula is to increase the workday from 5 to 6 to include Saturday as a workday.

    Please Login or Register  to view this content.
    Hope this helps?
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Counting Weekdays + Saturday, omit holidays

    Perhaps something like this:

    With
    A31:A41 containing your list of holiday dates

    C1: (a start date)
    D1: (an end date)

    This formula returns the number of workdays (Monday thru Saturday) in that date range LESS any holidays in that range:
    E1: =SUM(INT((WEEKDAY(C1-{2,3,4,5,6,7})+D1-C1)/7))-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX($A:$A,C1):INDEX($A:$A,D1)),$A$31:$A$41,0)))

    Note: there are no spaces in that formula
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Your current formula is slightly flawed because the last part

    WEEKDAY(A31:A41)<>1

    only checks that the holidays aren't Sundays, this part should really be

    WEEKDAY(A31:A41,2)<6

    otherwise you could get an incorrect count where the previous month contains a Saturday holiday,

    You could amend the formula to count all days except Saturdays but I think you can use a slightly simpler formula, i.e. to count all weekdays (except holidays)

    =SUMPRODUCT(--(WEEKDAY(C1+1-ROW(INDIRECT("1:"&DAY(C1))),2)<6),--ISNA(MATCH(C1+1-ROW(INDIRECT("1:"&DAY(C1))),A31:A41,0)))

    To change this to count all weekdays and Saturdays (except holidays) change the 6 to a 7

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Perhaps a more efficient formula, along similar lines to Ron's suggestion, to count all workdays in the calendar month up to and including C1, including Sats but excluding holidays

    =DAY(C1)-INT((DAY(C1)-WEEKDAY(C1))/7)-1-SUMPRODUCT((WEEKDAY(A31:A41)>1)*(A31:A41>C1-DAY(C1))*(A31:A41<=C1))

  6. #6
    Registered User
    Join Date
    07-30-2007
    Posts
    14
    I really appreciate everyone's help! Wasn't expecting such rapid replies. This forum is great!

    Quote Originally Posted by daddylonglegs
    Perhaps a more efficient formula, along similar lines to Ron's suggestion, to count all workdays in the calendar month up to and including C1, including Sats but excluding holidays

    =DAY(C1)-INT((DAY(C1)-WEEKDAY(C1))/7)-1-SUMPRODUCT((WEEKDAY(A31:A41)>1)*(A31:A41>C1-DAY(C1))*(A31:A41<=C1))
    Could this formula also work to exclude Saturdays? I'm all about efficient formulas and this is the shortest by far (excluding NETWORKDAYS formulas).

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Excluding Saturdays

    =SUM(INT((DAY(C1)-WEEKDAY(C1-{1,2,3,4,5}))/7)+1)-SUMPRODUCT((WEEKDAY(A31:A41,2)<6)*(A31:A41>C1-DAY(C1))*(A31:A41<=C1))

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Hello bryce, re your pm, here's an explanation of the following formula:

    =SUMPRODUCT(--(WEEKDAY(C1+1-ROW(INDIRECT("1:"&DAY(C1))),2)<6),--ISNA(MATCH(C1+1-ROW(INDIRECT("1:"&DAY(C1))),A31:A41,0)))


    let's start with this part

    --(WEEKDAY($C$1+1-ROW(INDIRECT("1:"&DAY($C$1))),2)<6)

    Let's assume C1 is today, 5th feb.

    Then ROW(INDIRECT("1:"&DAY($C$1)))

    becomes

    ROW(INDIRECT("1:5"))

    which generates an array from 1 to 5, i.e

    {1;2;3;4;5}

    [You don't strictly need INDIRECT for the formula to work in a single cell but it makes it more "robust" so that if you copy the formula or delete or insert rows it'll still work as intended]

    So C1+1-{1;2;3;4;5}

    just generates an array of all this month's dates so far, excel stores these as serial numbers so you get

    WEEKDAY({39480;39481;39482,39483;39484},2)

    WEEKDAY(date,2) returns a number from 1 to 7, 1 is Monday through to 7, Sunday so you get

    {5;6;7;1;2}

    representing Fri, Sat, Sun, Mon, Tue

    of course when we add the test <6 we get

    {TRUE;FALSE;FALSE;TRUE;TRUE}

    i.e. TRUE for weekdays, false for weekends

    -- then converts TRUE to 1 and 0 to FALSE so you get

    {1;0;0;1;1}

    Of course the second part works in a similar way.....

    This

    --ISNA(MATCH(C1+1-ROW(INDIRECT("1:"&DAY(C1))),A31:A41,0))

    becomes

    --ISNA(MATCH({39480;39481;39482,39483;39484},A31:A41,0))

    so this matches each date in the month so far against the holiday list, if there's a match a number is returned [position of the matched date in the holiday list] otherwise we get #N/A, so in this case lets pretend that 1st february is a holiday in your list......you then get something like

    --ISNA({4;#N/A;#N/A,#N/A;#N/A})

    which gives

    {0;1;1;1;1}

    so now the SUMPRODUCT formula is reduced to

    =SUMPRODUCT({1;0;0;1;1},{0;1;1;1;1})

    The first array represents weekday/weekend 1/0 and the second workday/holiday 1/0.

    We want to count the days which are both 1, i.e. weekdays which aren't holidays and SUMPRODUCT gives us that because the arrays are multiplied to give

    {0;0;0;1;1}

    and then summed to give 2

    which is the number of working days so far in the month (assuming 1st Feb was a holiday)

    regards
    daddylonglegs

  9. #9
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    Finish Date (Include Saturdays) using duration

    Question/Reply was removed
    Last edited by lil_ern63; 05-20-2008 at 12:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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