+ Reply to Thread
Results 1 to 17 of 17

How to set dynamic holidays in NETWORKDAYS.INTL() Function

  1. #1
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    How to set dynamic holidays in NETWORKDAYS.INTL() Function

    Hi,
    I have an issue with function “NETWORKDAYS.INTL”. I want the holidays in this function to be more dynamic.

    I want, for example, to exclude only holidays for month 12/2019, month 11/2019, month 10/2019, and so on.

    This is my situation (Excel file is attached):
    =NETWORKDAYS.INTL(H14,H15,7,{"11/12/2019","25/12/2019"})
    I want to replace holidays ({"11/12/2019","25/12/2019"}) to be dynamic from range A3:A14 and only retrieve holidays between H14 and H15.

    Note: The date format I used in the Attached Excel file is "dd/mm/yyyy".


    Thanks a lot.
    Attached Files Attached Files
    Last edited by Abdul Khalifeh; 12-20-2019 at 08:07 AM.

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,635

    Re: NETWORKDAYS.INTL() Function

    Hi
    from MS support
    Holidays Optional. An optional set of one or more dates that are to be excluded from the working day calendar. holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.

  3. #3
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: NETWORKDAYS.INTL() Function

    Hi Pepe,
    thanks for the reply.
    In my situation the holidays is mandatory and it's changes from year to year, so I need to make it variables not constant.

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,635

    Re: NETWORKDAYS.INTL() Function

    Well you can change the range each year?
    Otherwise VBA will probably be needed

  5. #5
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: NETWORKDAYS.INTL() Function

    I have to save the history of holidays.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,187

    Re: NETWORKDAYS.INTL() Function

    You could put the holidays for each year in separate columns where the year is in row 1. You could then use an index match as the holiday argument to the NETWORKDAYS.INTL function as in the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For more specific help using your data please upload a file using the instructions in the banner at the top of the page.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: NETWORKDAYS.INTL() Function

    Hi JeteMc,
    Thanks for your reply.
    Please let me know how to reflect your solution on the image attached.

    Thanks a lot.

    Attachment 653969

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,635

    Re: NETWORKDAYS.INTL() Function

    Don't you think a sheet would be easier to work with than a useless picture ?

  9. #9
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: NETWORKDAYS.INTL() Function

    Of course. The excel sheet is attached. Thanks.Attachment 653971

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,627

    Re: NETWORKDAYS.INTL() Function

    Abdul Khalifeh what you have posted is useless. We need an actual Excel workbook upload. Please read and follow the instructions in the 'gold' banner at the top of the page. Your name will be the first words.
    Dave

  11. #11
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: NETWORKDAYS.INTL() Function

    I already uploaded an actual Excel Workbook using "insert Image" link.

  12. #12
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,346

    Re: NETWORKDAYS.INTL() Function

    Hi abdul, that was not the correct way to upload the file, you cannot use the image link to upload excel files, we cannot access it now.

    you need to use a different method. Please read instructions below on how to upload an excel file:

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,895

    Re: NETWORKDAYS.INTL() Function

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  14. #14
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: How to set holidays dynamic using NETWORKDAYS.INTL() Function

    Hi,
    My issue with function “NETWORKDAYS.INTL” is that I want the holidays in this function to be more dynamic.

    I want, for example, to exclude only holidays for month 12/2019, month 11/2019, month 10/2019, and so on.

    This is my situation (Excel file is attached):
    =NETWORKDAYS.INTL(H14,H15,7,{"11/12/2019","25/12/2019"})
    I want to replace holidays ({"11/12/2019","25/12/2019"}) to be dynamic from range A3:A14 and only retrieve holidays between H14 and H15.

    Note: The date format I used in the Attached Excel file is "dd/mm/yyyy".


    Thanks.
    Attached Files Attached Files

  15. #15
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,346

    Re: How to set dynamic holidays in NETWORKDAYS.INTL() Function

    Hi Abdul,

    Thanks for the file. I'm affraid I do not see any problem with the formula, you have replaced the fixed array {"11/12/2019","25/12/2019"} with the range A3:A14 that is the only thing you need to do.

    using the A3:A14 range for 2019 holidays is fine. If our start and enddate is december only the NETWORKDAYS.INTL will ignore the holidays of other months and only exclude the non-working days of december in the count of the net workdays. total workdays in 2019 is 21 based on friday saterday weekend. because dec 11 and dec 25 are holidays.
    in 2020 dec 11 and dec 25 are both on a friday so already in a weekend so workdays is 23.

    the outcome of the function is correct with the full range A3:A14 (or D3:D20) in the formula for all months. there is no reason to limit the range to only the holidays for that selected month.

    Edit: I have attached the file with the verification of the no of workdays and also "my version" of the formula for all months of 2019 and 2020. that may help you understand the usage.
    I recommend keeping holidays of all the years in the same column and also make it an excel table. So there is no need to change the formula if the year changes. the oldest years can be removed if they are no longer relevant for the use in the sheet. but there is no harm in having the older years in and extending the range as needed.
    Attached Files Attached Files
    Last edited by Roel Jongman; 12-21-2019 at 12:35 PM.

  16. #16
    Registered User
    Join Date
    12-11-2019
    Location
    Ramallah
    MS-Off Ver
    2016
    Posts
    13

    Re: How to set dynamic holidays in NETWORKDAYS.INTL() Function

    Hi Roel,
    Thanks so much for the reply.

    I was thought that the NETWORKDAYS.INTL() function ignores all dates in the holiday ranges regardless the specified start and end date. Thanks a lot . My appreciated.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,895

    Re: How to set dynamic holidays in NETWORKDAYS.INTL() Function

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

+ 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. =networkdays.intl
    By Southie89 in forum Excel General
    Replies: 5
    Last Post: 05-11-2018, 11:07 AM
  2. Flexible holidays in NETWORKDAYS.INTL()
    By Rionce in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-04-2018, 03:11 PM
  3. Combining NETWORKDAYS.INTL with IF formula?
    By Adelita in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-29-2016, 07:01 AM
  4. NETWORKDAYS.INTL in 2007
    By nickh1981 in forum Excel General
    Replies: 4
    Last Post: 08-11-2015, 02:45 AM
  5. [SOLVED] Networkdays.intl
    By andrecamapum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-30-2015, 09:31 AM
  6. [SOLVED] Networkdays.Intl, incorrect result in one row
    By razaas in forum Excel General
    Replies: 1
    Last Post: 10-22-2014, 07:04 AM
  7. [SOLVED] Looking for alternative to NETWORKDAYS.INTL
    By Jaron_t in forum Excel General
    Replies: 4
    Last Post: 05-21-2012, 04:54 PM

Tags for this Thread

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