+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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,217

    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?

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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