+ Reply to Thread
Results 1 to 28 of 28

Power Query NETWORKDAYS function

  1. #1
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Power Query NETWORKDAYS function

    I've done some googling and can't find an easy-to-follow walkthrough on recreating a NETWORKDAYS functions using Power Query.

    Does anybody have an idiot-proof step-by-step they could point me to?

    Where I'm at.....

    I have a Power Query query that contains a table of dates, one row for each date.
    I have a second column which has a "Yes" if the date in that row is a public holiday.
    I have a third column that uses a formula to give a day of the week from 0 to 6 for each date.
    I have a fourth column that contains a 1 if the date is a Working Day, and a zero if it is not a Working Day.
    The last step is I filter so only workday dates are included in the list.

    That's where I'm stuck, trying to create the function to apply to my start and end date.
    Attached Files Attached Files
    Last edited by kersplash; 07-19-2018 at 01:41 AM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: Power Query NETWORKDAYS function

    Try this - it will calculate Net Workdays between a Start and End date, taking into account a list of Holiday dates in a specified table:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    Thanks Olly,

    Only new to Power Query so if you are able to implement that code into my workbook that would be appreciated.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    in your example source table with dates (startdate, enddate) doesn't exist.
    networkdays are counted between two dates so how do you want do that with your example file?
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    There you go.

    EDIT - Removed Incorrect file
    Last edited by kersplash; 07-19-2018 at 11:35 PM.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    Are you sure this is correct file?

    maybe something like this:

    StartDate EndDate StartDate EndDate Workdays Formula
    01/01/2018
    10/07/2018
    01/01/2018
    10/07/2018
    131
    131
    20/07/2018
    20/08/2018
    20/07/2018
    20/08/2018
    22
    22
    01/01/2017
    02/01/2017
    01/01/2017
    02/01/2017
    0
    0
    20/07/2018
    23/07/2018
    20/07/2018
    23/07/2018
    2
    2
    Sheet: Networkdays

  7. #7
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    My bad, here you go.
    Attached Files Attached Files

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Power Query NETWORKDAYS function

    it was so hard? (joke)

  9. #9
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    Thanks Sandy...still learning.

    I tried adding 2/01/2018 to my public holiday list and refreshed it, the Networkdays value in the first row should be 0, but stays at 1?

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    ask author

    and change date format from Armenian locale to English Aussie or something

    edit: maybe somwhere you need to add (-1) ? who knows?
    Last edited by sandy666; 07-19-2018 at 11:55 PM.

  11. #11
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    Here's what I have now.

    I've added the excel networkdays calculation to my source data, and the two columns should be the same....but are not.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    better tell me if you discovered what I did to make it work

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    add metworkdays.intl outside of any table and check

    Start Date End Date Start Date End Date Networkdays
    01/01/2018
    05/01/2018
    01/01/2018
    05/01/2018
    4
    4
    10/07/2018
    27/08/2018
    10/07/2018
    27/08/2018
    34
    35


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by sandy666; 07-20-2018 at 12:11 AM.

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    with my PQ Networkdays I added to holidays table dates: 01/01/2018 - 02/01/2018

    StartDate EndDate StartDate EndDate Workdays Formula
    01/01/2018
    10/07/2018
    01/01/2018
    10/07/2018
    130
    130
    20/07/2018
    20/08/2018
    20/07/2018
    20/08/2018
    22
    22
    01/01/2017
    02/01/2017
    01/01/2017
    02/01/2017
    0
    0
    20/07/2018
    23/07/2018
    20/07/2018
    23/07/2018
    2
    2
    01/01/2018
    02/01/2018
    01/01/2018
    02/01/2018
    0
    0


    and it works, so you need ask Olly why....

  15. #15
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    I'm not sure what you're trying to tell me?

    You have a solution that works but I need to ask the author why their solution doesn't?

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    correct
    we have two different solutions.
    yours was adapted from here (probably)

  17. #17
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    OK.....

    I am open to other solutions.

    Maybe I'm missing something in the Armenian to English translation?

  18. #18
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    Sorry but I don't know what you did so I can't say it was correct or not

    but this is a format date only

  19. #19
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    We're going around in circles here.

    If anyone has an answer to getting the NETWORKDAYS functionality into Power Query I'd love to see what they have in a sample spreadsheet.

  20. #20
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Power Query NETWORKDAYS function

    So you'll never learn my friend

    have a nice day

  21. #21
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: Power Query NETWORKDAYS function

    My query doesn't (currently) handle the start or end day being a non-working day, as written. Not sure what sort of use-case there would be, for a NetWorkdays function between non-working days...

  22. #22
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: Power Query NETWORKDAYS function

    Here you go - this is probably a slightly more effective approach, which will handle passing non-working days as Start / End dates.

    Please Login or Register  to view this content.
    See attachment for worked example.
    Attached Files Attached Files

  23. #23
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Power Query NETWORKDAYS function

    Thanks Olly for taking the time. Works perfectly and appreciate the help.

  24. #24
    Registered User
    Join Date
    12-13-2019
    Location
    dallas, Texas
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Power Query NETWORKDAYS function

    Doesn't work. Your power query function only calculates between dates, but it does not at all take into account the holidays table.
    PowerQueryFail.JPG

  25. #25
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: Power Query NETWORKDAYS function

    Does work. You're obviously using it wrong.

  26. #26
    Registered User
    Join Date
    12-13-2019
    Location
    dallas, Texas
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Power Query NETWORKDAYS function

    I ran a simple test (posted a picture showing where I added 2 dates to the holidays table to test). Excel Funtion gets it correct as you can see, your power query function does not work for Holidays only skips weekends.

  27. #27
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: Power Query NETWORKDAYS function

    I bet your table and column names don't match the query.

    Post your workbook.

  28. #28
    Registered User
    Join Date
    12-13-2019
    Location
    dallas, Texas
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Power Query NETWORKDAYS function

    Appologies Olly, your 07-23-2018, 12:44 PM workbook works, I downloaded a workbook in a prior post. Please disregard and thank you for following up.

+ 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. Sub-Forum for Power Query & Power BI?
    By AliGW in forum Suggestions for Improvement
    Replies: 7
    Last Post: 08-26-2018, 05:25 PM
  2. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  3. New to Power Query Need to refence a cell for SQL query
    By BSWhipp in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-10-2018, 09:00 AM
  4. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  5. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  6. Power Query - Invoke Custom Function
    By leukep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2017, 02:18 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