Forum Discussion

MaJar's avatar
MaJar
Frequent Visitor
2 years ago

Calculate Working hours between 2 dates in a specified interval

Hi All,

Knowing that I have 4 columns in my dataset, I want to create a custom function in power query that will have these 4 attributes:

- Start date as DateTime (Example: 05/05/2023 05:45:54 AM)

- End date as DateTime (Example: 07/05/2023 02:56:34 PM)

-Work Start as Time (Example: 08:30:00 AM)

-Work End as Time (Example: 05:30:00 PM)

 

This function will invoke a custom column that will calculate the total working hours between the start date and end date in the interval of the work start and work end.

 

Thank you in advance for the help.

Need Help
Tips and Tricks

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    This has been covered multiple times recently.

     

    You need to bring your own definition of 

    - workdays

    - working hours

    - holidays and weekends

    - what should happen when events start or end outside of working hours.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MaJar,

    Here is the custom function to calculate total second from the start date, end date based on specific work time ranges:

    let
    WorkDuration = (startDate as datetime, enddate as datetime, workstart as time, workend as time) =>
        let
            _start = Date.From(startDate) & List.Max({Time.From(startDate), workstart}),
            _end = Date.From(enddate) & List.Min({Time.From(enddate), workend}),
            IsCrossDays = if Date.From(startDate) = Date.From(enddate) then true else false,
            result =
                if IsCrossDays = false then
                    Duration.TotalSeconds(_end - _start)
                else
                    Duration.TotalSeconds(Date.From(startDate) & workend - _start) + 
                    Number.Round(Duration.TotalDays(_end - _start)-1,0) * Duration.TotalSeconds(workend - workstart) + 
                    Duration.TotalSeconds(_end - Date.From(enddate) & workstart)
        in
            result
in
    WorkDuration

    You can add a new custom column to invoke this custom function with current row field values.

    Regards,

    Xiaoxin Sheng