Date/Time Difference taking into consideration business hours 09:00 to 17:30

[SachinC]

Hi there,

How do i calculate this please?  Currently I am getting difference in minutes which is 700+ particularly when it's 8pm and 8am.

This is to calculate difference in minutes for helpdesk stats - our helpdesk is manned 09:00 to 17:30 Mon-Fri.
Thanks,

S

[Ashish_Mathur]

Hi,

I think this will be a tough one to solve in DAX/M.  I have an Excel solution here though.

Hope this helps.

[MarcelBeug · Accepted solution]

If I understand correctly, you want to calculate the total minutes during business hours between start datetime and end datetime.

Below a Power Query solution that takes into consideration holidays, workingdays, start/end time of business hours.

Holidays is a table with column Holiday and contains dates (of holidays).

Table1 is the table with start datetimes and end datetimes.

StartTime and EndTime are parameters in Power Query containing the start and end time of business hours.

WorkingDays is a table with day numbers of workingdays (0..6 --> Su..Sa):

= Table.FromColumns({{1..5}},type table[DayOfWeek = Int64.Type])

Shortly summarized, the query below creates a calendar list / table for each input row, containing each date with weekday and start/end datetimes, removes weekends and holidays, and calculates the total duration minutes.

let
    Source = Table1,
    AddedDates = Table.AddColumn(Source, 
                                 "Dates", 
                                 each List.Transform({Number.RoundDown(Number.From([Start]))..
                                                          Number.RoundDown(Number.From([End]))},
                                                     Date.From),
                                 type {date}),
    AddedStartAndEnd = Table.TransformColumns(AddedDates,
                                              {{"Dates", 
                                                each List.Transform(_, each {_, _ & StartTime, _ & EndTime}),
                                                type {datetime}}}),
    AddedDateTimesTable = Table.AddColumn(AddedStartAndEnd, 
                                          "DateTimes",
                                          (x) => Table.FromRows(
                                                     List.Transform(
                                                         x[Dates], 
                                                         each {_{0}, 
                                                               Date.DayOfWeek(_{0},Day.Sunday), 
                                                               List.Max({x[Start],_{1}}),
                                                               List.Min({x[End],_{2}})}),
                                                     type table[Date = date,DayOfWeek = Int64.Type,Start = datetime,End = datetime])),
    SkipWeekends = Table.TransformColumns(AddedDateTimesTable,
                                          {{"DateTimes", 
                                            each Table.NestedJoin(_,{"DayOfWeek"},WorkingDays,{"DayOfWeek"},"WeekEnd",JoinKind.Inner)}}),
    SkipHolidays = Table.TransformColumns(SkipWeekends,
                                         {{"DateTimes", 
                                           each Table.NestedJoin(_,{"Date"},Holidays,{"Holiday"},"Holiday",JoinKind.LeftAnti)}}),
    TotalMinutes = Table.AddColumn(SkipHolidays, 
                                   "Total Minutes", 
                                   each Duration.TotalMinutes(
                                            List.Sum(
                                                Table.TransformRows(
                                                    _[DateTimes], 
                                                    each List.Max({#duration(0,0,0,0),[End]-[Start]})))),
                                   Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(TotalMinutes,{"Dates", "DateTimes"})
in
    #"Removed Columns"

[SachinC]

Excellent reply - sorry for being thick, can you explain how I can get your formula implemented please?  I need a step-by-step guide.  Also, my fields for start time is called [Request date] and the end time is called [Assigned date].

I look forward to your response.

Thanking you in advance.

[MarcelBeug]

You can copy/paste the code in the Query Editor - Advanced Editor and adjust according to your table/column names.

Don't forget the other objects I mentioned.

[SachinC]

@MarcelBeug - do I create separate tables for Holidays etc...

Regards,

S

[MarcelBeug]

Yes. Below the query objects I created in Excel, which would be similar in Power BI.

Table1 contains the input data; TurnAround creates the output.

[SachinC]

Can you attach your Excel file please? Thank you.

[MarcelBeug]

Excel file

You can also find an alternative solution on Technet.

[Interkoubess]

Hi @SachinC,

Cou you please give a sample data with your expected results?

Thank you