Calculate Working hours between 2 dates in a specified interval

[MaJar]

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.

[Anonymous]

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

[lbendlin]

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-...

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/1447...