+ Reply to Thread
Results 1 to 5 of 5

Count a specific day week between 2 dates without bank holiday

  1. #1
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Count a specific day week between 2 dates without bank holiday

    Hello,

    I would like to ask some support to count a specific day between 2 dates.
    How should l modify (or write another) the below mentioned formula if I would like to know that how many Sundays are in July 2019 but without bank holidays?

    The formula:
    Please Login or Register  to view this content.
    The dates:
    date1 = 1 July 2019
    date2 = 31 July 2019
    bank holiday = 14 July 2019

    The formula should return 3, because Sunday, 14 July is bank holiday in this example!

    Thank you in advance the support!

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Count a specific day week between 2 dates without bank holiday

    Use the NETWORKDAYS.INTL function, which has the following format:
    =NETWORKDAYS.INTL( start date ; end date ; weekend days ; holidays )
    You can specify specific working / weekend days using a string of seven 1s or 0s, with 1 meaning 'weekend/non-workday' and 0 meaning 'workday' - starting with Monday. For example, "1000111" will count only Tuesdays, Wednesdays and Thursdays as working days.
    Holidays are specified with a range, so you can list all your holidays in one place and just refer to that range here - those dates will then be excluded from the 'workday' count.

    To take your specific example, let's say you put 1 July into A1, 31 July into B1 and 14 July into C1. This formula will return '3' as you want:
    =NETWORKDAYS.INTL(A1,B1,"1111110",C1)

    Obviously you can modify the string to change the specific day you want to count.

    Hope that helps.


    Edit: the function is documented here: https://support.office.com/en-gb/art...8-4E925BFD5E28
    Last edited by Aardigspook; 07-16-2019 at 04:34 PM.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Re: Count a specific day week between 2 dates without bank holiday

    Thank you the help!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count a specific day week between 2 dates without bank holiday

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Count a specific day week between 2 dates without bank holiday

    You're welcome, glad I could help and thanks for the rep.

+ 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. [SOLVED] Highlight UK Bank Holiday
    By ~TaC~ in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-08-2019, 12:39 AM
  2. Count dates in a row within a specific week.
    By Wijnand1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2018, 10:22 AM
  3. Count days of week based on dates and specific text
    By elv28 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-20-2018, 01:23 AM
  4. including bank holiday's within my workbook
    By ellie247 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2015, 08:23 AM
  5. [SOLVED] IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates
    By jowarks in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2013, 05:42 AM
  6. holiday calculation around bank holidays
    By y0rk1e72 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-19-2012, 06:55 AM
  7. [SOLVED] Dates, Bank Holidays and a Working Week
    By jennyaccord in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 04:43 AM

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