+ Reply to Thread
Results 1 to 10 of 10

Power Query function to calculate workdays including decimals between datetimes

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Power Query function to calculate workdays including decimals between datetimes

    Hi,

    I have been searching the internet to find a custom function for Power Query that will calculate networkdays in a decimal format, between two date columns, where either date may be formatted as either date or datetime. I have only found solutions which require the dates to be formatted as date, and none which work with datetime. The function should be able to exclude weekends and holidays according to a custom table.

    Does anybody know of a workable solution for this?

    Regards,
    Marbleking

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Power Query function to calculate workdays including decimals between datetimes

    Look at this link. I am not sure what you mean by decimal format, but once you have the dates you can re-format them as needed.

    https://www.thebiccountant.com/2020/...-and-power-bi/
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Power Query function to calculate workdays including decimals between datetimes

    Hi alansidman,

    I am using the custom function you linked to, but it requires dates and returns error if the data contains datetime. I want to be able to subtract datetimes and, ideally, get days including fraction of days, but at least whole days - and remove weekends and holidays.

    Regards,
    Marbleking

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Power Query function to calculate workdays including decimals between datetimes

    Can you convert your datetime to a decimal format? Is that your issue that it is not convertible? What about converting it before bringing it into power query? Can you guess that I am grasping at straws for a solution that will work for you without seeing your data?

  5. #5
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Power Query function to calculate workdays including decimals between datetimes

    Hi alansidman,

    Attached is an example of my data. I have calculated the desired results in column G. Also, I have alternative results in column H, which would be very useful, where empty cells in End_date is substituted with NOW().

    The formula is based on information from this site:

    https://www.exceltactics.com/calcula...g-networkdays/

    It would be great if these two functions (column G and H) could be written as custom functions for Power Query!

    (PS: To avoid getting #DIV/0! errors when all hours of the workdays are used, I have written 00:00:00 in Start_time and 23:59:59,9 in End_Time).

    Best regards,
    Marbleking
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Power Query function to calculate workdays including decimals between datetimes

    I have played with this and it is above my pay grade. Cannot even get the function from Matt to work. Good Luck with finding a solution. I would however suggest that you formatting your dates to decimals in Native Excel before bringing the data into PQ. It may make it easier.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query function to calculate workdays including decimals between datetimes

    Try formula

    =IF(A2,MAX(0,SUM(CHOOSE({1,2,3},IF($E$2,$E$2,1)-$D$2,-MAX(0,MIN(IF($E$2,$E$2,1),MOD(A2,1))-$D$2),-MAX(0,IF($E$2,$E$2,1)-MAX($D$2,MOD(IF(B2,B2,NOW()),1))))*NETWORKDAYS.INTL(IF({1,1,0},A2,IF(B2,B2,NOW())),IF({0,1,0},A2,IF(B2,B2,NOW())),1,HolidayList!$B$2:$B$196))),)



    or PQ

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Power Query function to calculate workdays including decimals between datetimes

    Really great, Bo_Ry! Thanks. How can the PQ solution be tweaked so that it can be invoked as a reusable custom function?

    Also; I altered the code to allow for getting negative values if St > En, but perhaps there's a better way:

    "if St = null then 0 else if St > En then Number.From(En - St) else Res ,type number)"
    Last edited by Marbleking; 11-11-2021 at 03:20 AM.

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query function to calculate workdays including decimals between datetimes

    Last try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Power Query function to calculate workdays including decimals between datetimes

    Thanks a million, Bo_ry!

+ 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] Convert text dates and datetimes to formatted number datetimes
    By Marbleking in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-18-2021, 11:02 AM
  2. [SOLVED] Net workdays in Power Query
    By Frejo in forum Excel General
    Replies: 4
    Last Post: 12-17-2020, 10:53 AM
  3. Power Query calculate Count numbers by different dates
    By k2hunter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-02-2020, 06:01 PM
  4. 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
  5. Replies: 15
    Last Post: 03-29-2018, 11:29 AM
  6. how to calculate time elapsed in 2 datetimes?
    By jamiguel77 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2013, 06:19 PM
  7. Calculate workdays between two dates (including Saturdays, excluding holidays)
    By Sean Anderson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2007, 12:22 AM

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