+ Reply to Thread
Results 1 to 5 of 5

Holidays not working in NETWORKDAYS Function

  1. #1
    Registered User
    Join Date
    12-17-2020
    Location
    Auckland, New Zealand
    MS-Off Ver
    MS 365 for business
    Posts
    25

    Holidays not working in NETWORKDAYS Function

    Hi Community,

    I'm using the following formula to work out how many working days there are in the month.

    =NETWORKDAYS(A2,EOMONTH(A2,0),Holidays[Date])

    We have 3 public holidays here in NZ in Jan, but for some reason, the formula I'm using is not picking up the data I've designated for holidays.
    It would be a great help if someone can spot where I'm going wrong!

    Book Holidays.xlsx

    Obviously, as the month changes the public holidays should be taken into account

    Thanks
    Tony

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,336

    Re: Holidays not working in NETWORKDAYS Function

    Hi,

    Why do you believe the answer is 18 & not 19?

    There are 21 non weekend days, and you have two public holidays, 1 Jan and 27 Jan. You can't deduct 2 Jan since that's a Saturday and is already excluded from the 21 days
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-17-2020
    Location
    Auckland, New Zealand
    MS-Off Ver
    MS 365 for business
    Posts
    25

    Re: Holidays not working in NETWORKDAYS Function

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Why do you believe the answer is 18 & not 19?

    There are 21 non weekend days, and you have two public holidays, 1 Jan and 27 Jan. You can't deduct 2 Jan since that's a Saturday and is already excluded from the 21 days
    We have three public holidays here in NZ in Jan.
    1st
    2nd
    27th

    So it should be 18 - I cant see why its not working ¯\_(ツ)_/¯

  4. #4
    Registered User
    Join Date
    12-17-2020
    Location
    Auckland, New Zealand
    MS-Off Ver
    MS 365 for business
    Posts
    25

    Re: Holidays not working in NETWORKDAYS Function

    Let me move the 2nd to the 4th - and retry it.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,963

    Re: Holidays not working in NETWORKDAYS Function

    In Jan, there are 10 weekend days (2,3,9,10,16,17,23,24,30,31) and 3 holidays (1,2,27), total is 10+3=13, but 2nd day is weekend and also holiday, so be counted 1 only , then total should be =13-1=12

    Working day of Jan should be = 31-12=19 days.

+ 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] How to set dynamic holidays in NETWORKDAYS.INTL() Function
    By Abdul Khalifeh in forum Excel General
    Replies: 16
    Last Post: 12-22-2019, 02:43 AM
  2. Networkdays function not working as expected
    By Rem0ram in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-02-2014, 09:29 AM
  3. Networkdays function not working?
    By sherringtonjr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-30-2013, 11:01 AM
  4. [SOLVED] NETWORKDAYS with a row of Holidays
    By dsepshn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 06:01 AM
  5. [SOLVED] IF function not working with dates and NETWORKDAYS
    By Hendar in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-08-2013, 06:49 AM
  6. Holidays and Networkdays
    By tnoble17 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2010, 10:31 PM
  7. [SOLVED] Networkdays Function Not Working
    By Al H. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2005, 11:06 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