Response time during business hours excluding weekends and lunch break

[ouss102]

Hello,

I need to calculate a response time between two dates in hours, the calcultation needs to exclude the weekends, holidays, lunch break from 12:00 to 13:30 and takes in acount the business hours that are from 08:00 to 16:30, I am using this Power Quey formula but it doeesn't exclude lunch breaks.

Thanks in advance for your help !

------------------------------------------------------

(WHStart, WHEnd, Start, End, ListOfHolidays) =>

let

DStart = Number.From(DateTime.Date(Start)), // start day
TStart = Number.From(DateTime.Time(Start)), // start time
DEnd = Number.From(DateTime.Date(End)), // end day
TEnd = Number.From(DateTime.Time(End)), // end time
// List of days without saturdays and sundays
ListOfNumbers = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
// List of dates without holidays, saturdays and sundays
ListOfWorkingDays = List.Difference(ListOfNumbers,ListOfHolidays),
SumOfWorkingHours = 24*(if DStart = DEnd then //checking if the start day is the same as the final day
if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
else
0
else
(
if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
else
0
)
+
(
if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
else
0
)
+
// sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart))
in
SumOfWorkingHours

----------------------------------

Example of data

Start date	End date	Expected result
13/03/2023 08:00	13/03/2023 15:30	6 h
13/03/2023 08:00	13/03/2023 17:00	7 h
13/03/2023 08:00	13/03/2023 16:30	7 h
13/03/2023 08:00	13/03/2023 13:00	4 h
13/03/2023 08:00	14/03/2023 16:30	14 h
10/03/2023 08:00	13/03/2023 16:30	14 h

[Adamboer]

To exclude the lunch break from the calculation, you can modify the formula as follows:

(WHStart, WHEnd, Start, End, ListOfHolidays) =>

let
DStart = Number.From(DateTime.Date(Start)), // start day
TStart = Number.From(DateTime.Time(Start)), // start time
DEnd = Number.From(DateTime.Date(End)), // end day
TEnd = Number.From(DateTime.Time(End)), // end time
// List of days without saturdays and sundays
ListOfNumbers = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
// List of dates without holidays, saturdays and sundays
ListOfWorkingDays = List.Difference(ListOfNumbers,ListOfHolidays),
// Calculate the duration of the lunch break in seconds
LunchBreakDuration = Number.From(Duration.FromText("01:30:00")) - Number.From(Duration.FromText("01:00:00")),
// Determine the start and end times of the lunch break
LunchBreakStart = Number.From(DateTime.Time(#time(12,0,0))),
LunchBreakEnd = LunchBreakStart + LunchBreakDuration,
// Function to calculate the duration in seconds between two times, excluding the lunch break
CalculateDuration = (StartTime, EndTime) =>
let
// Determine if the start time is before, during, or after the lunch break
StartBeforeLunch = StartTime < LunchBreakStart,
StartDuringLunch = StartTime >= LunchBreakStart and StartTime < LunchBreakEnd,
StartAfterLunch = StartTime >= LunchBreakEnd,
// Determine if the end time is before, during, or after the lunch break
EndBeforeLunch = EndTime < LunchBreakStart,
EndDuringLunch = EndTime >= LunchBreakStart and EndTime < LunchBreakEnd,
EndAfterLunch = EndTime >= LunchBreakEnd,
// Calculate the duration in seconds
DurationBeforeLunch = if StartBeforeLunch and EndBeforeLunch then EndTime - StartTime else if StartBeforeLunch and EndDuringLunch then LunchBreakStart - StartTime else if StartBeforeLunch and EndAfterLunch then (LunchBreakStart - StartTime) + (EndTime - LunchBreakEnd) else 0,
DurationDuringLunch = if StartDuringLunch and EndDuringLunch then 0 else if StartDuringLunch and EndAfterLunch then EndTime - LunchBreakEnd else if StartAfterLunch and EndDuringLunch then LunchBreakStart - StartTime else if StartBeforeLunch and EndAfterLunch then LunchBreakDuration else 0,
DurationAfterLunch = if StartAfterLunch and EndAfterLunch then EndTime - StartTime else if StartAfterLunch and EndBeforeLunch then EndTime - LunchBreakEnd else if StartBeforeLunch and EndAfterLunch then (EndTime - LunchBreakEnd) + (LunchBreakStart - StartTime) else 0
in
DurationBeforeLunch + DurationDuringLunch + DurationAfterLunch,
// Calculate the duration in seconds between the start and end times, excluding weekends, holidays, and lunch breaks
DurationInSeconds = List.Sum(List.Transform(ListOfWorkingDays, each CalculateDuration(WHStart, WHEnd))),
// Convert the duration to hours
DurationInHours = Duration.FromSeconds(DurationInSeconds) / #duration(0, 0, 1, 0)
in
DurationInHours

This modified formula defines a function CalculateDuration that takes the start and end times of a period and calculates the duration in seconds between them, excluding

[v-stephen-msft]

Hi @ouss102 ,

The following are two methods for calculating working hours in Power Query using M or in Power BI Desktop using DAX, please refer to

Calculating Business Days and Hours Between Two Dates (Power Query) (antaresanalytics.net)

Calculating Business Days and Hours Between Two Dates (DAX) (antaresanalytics.net)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.