Need help calculating working hours difference between two dates

Hi PowerBigginer ,

Your Power Query code is likely failing because the logic to adjust the start and end times doesn't correctly handle all scenarios, particularly when a ticket is created or closed on a non-working day (like a weekend or holiday) or outside the 8 AM to 5 PM window. The errors arise when the code cannot find a valid working day or time to "snap" to.

The most effective solution is to use a robust, self-contained custom function that can accurately calculate the duration. This approach fixes the errors and is much cleaner than adding multiple intermediate columns to your table. You can create a new blank query, name it fxCalculateWorkingHours, and paste the following M code into the Advanced Editor. This function encapsulates all the complex logic for adjusting dates and calculating hours.

(
    StartDateTime as nullable datetime,
    EndDateTime as nullable datetime,
    optional Holidays as list,
    optional StartHour as number,
    optional EndHour as number
) as nullable number =>
let
    // 1. Define Parameters & Handle Nulls
    HolidaysList = if Holidays = null then {} else Holidays,
    WorkingStart = #time(if StartHour = null then 8 else StartHour, 0, 0),
    WorkingEnd = #time(if EndHour = null then 17 else EndHour, 0, 0),
    WorkingHoursPerDay = Duration.TotalHours(WorkingEnd - WorkingStart),

    ActualStart = StartDateTime,
    ActualEnd = if EndDateTime = null then DateTime.LocalNow() else EndDateTime,

    // Exit if no start date or if end is before start
    BailOut = ActualStart = null or ActualEnd < ActualStart,

    // 2. Helper function to find the next valid start time
    fnAdjustStart = (dt as datetime) =>
    let
        datePart = Date.From(dt),
        timePart = Time.From(dt),
        // Recursive check for the next working day
        FindNextWorkDate = (checkDate as date) =>
            if Date.DayOfWeek(checkDate, Day.Sunday) > 4 or List.Contains(HolidaysList, checkDate) then
                @FindNextWorkDate(Date.AddDays(checkDate, 1))
            else
                checkDate,
        // Determine the adjusted start
        AdjustedStart =
            let
                NextWorkDay = FindNextWorkDate(datePart)
            in
                if datePart < NextWorkDay then NextWorkDay + WorkingStart // Start was on a non-working day
                else if timePart >= WorkingEnd then FindNextWorkDate(Date.AddDays(datePart, 1)) + WorkingStart // Start was after hours
                else if timePart < WorkingStart then datePart + WorkingStart // Start was before hours
                else dt // Start was during work hours
    in
        AdjustedStart,

    // 3. Helper function to find the previous valid end time
    fnAdjustEnd = (dt as datetime) =>
    let
        datePart = Date.From(dt),
        timePart = Time.From(dt),
        // Recursive check for the previous working day
        FindPrevWorkDate = (checkDate as date) =>
            if Date.DayOfWeek(checkDate, Day.Sunday) > 4 or List.Contains(HolidaysList, checkDate) then
                @FindPrevWorkDate(Date.AddDays(checkDate, -1))
            else
                checkDate,
        // Determine the adjusted end
        AdjustedEnd =
            let
                PrevWorkDay = FindPrevWorkDate(datePart)
            in
                if datePart > PrevWorkDay then PrevWorkDay + WorkingEnd // End was on a non-working day
                else if timePart < WorkingStart then FindPrevWorkDate(Date.AddDays(datePart, -1)) + WorkingEnd // End was before hours
                else if timePart > WorkingEnd then datePart + WorkingEnd // End was after hours
                else dt // End was during work hours
    in
        AdjustedEnd,

    // 4. Main Calculation
    TotalHours = if BailOut then 0 else
    let
        // Adjust the start and end times using the helper functions
        AdjStart = fnAdjustStart(ActualStart),
        AdjEnd = fnAdjustEnd(ActualEnd),

        // If adjusted end is before adjusted start, duration is zero
        hours = if AdjEnd <= AdjStart then 0 else
        let
            StartDate = Date.From(AdjStart),
            EndDate = Date.From(AdjEnd),

            // Generate list of relevant dates
            DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),

            // Filter for working days only (Sunday-Thursday, excluding holidays)
            WorkingDaysList = List.Select(DateList, each Date.DayOfWeek(_, Day.Sunday) <= 4 and not List.Contains(HolidaysList, _)),

            // Calculate hours for each working day in the list
            HoursList = List.Transform(WorkingDaysList, each
                let
                   currentDate = _,
                   dayStart = if currentDate = StartDate then Time.From(AdjStart) else WorkingStart,
                   dayEnd = if currentDate = EndDate then Time.From(AdjEnd) else WorkingEnd
                in
                   Duration.TotalHours(dayEnd - dayStart)
            )
        in
            List.Sum(HoursList)
    in
        hours
in
    TotalHours
in
    fxCalculateWorkingHours

After creating the function, you can replace your existing table query with the much simpler script below. This code loads your data, defines your holidays, and then calls the fxCalculateWorkingHours function to add a column with the total working hours. A final step calculates the SLA_WorkingDays based on those hours, mirroring the logic from your DAX formula.

let
    // Load data
    Source = Sql.Database("RV2IVWPBI1P\RAYAMSSQLSERVER", "RayaOperationsData"),
    SR = Source{[Schema="dbo", Item="Fact_Siebel_ServiceRequest"]}[Data],
    
    // Change required column types
    ChangedTypes = Table.TransformColumnTypes(SR, {
        {"SR_CREATED", type datetime},
        {"SR_CLOSE_DATE", type datetime}
    }),

    // Define your list of holidays
    HolidayList = {
        #date(2025, 3, 30), #date(2025, 3, 31), #date(2025, 4, 1), #date(2025, 4, 2),
        #date(2025, 6, 5), #date(2025, 6, 8), #date(2025, 6, 9), #date(2025, 6, 10)
    },

    // Calculate Working Hours by invoking the custom function
    AddWorkingHours = Table.AddColumn(ChangedTypes, "WorkingHours", each 
        fxCalculateWorkingHours(
            [SR_CREATED], 
            [SR_CLOSE_DATE], 
            HolidayList,
            8, // Working Start Hour
            17 // Working End Hour
        ), 
        type number
    ),

    // Calculate SLA Days (This matches your DAX CEILING logic)
    AddSLADays = Table.AddColumn(AddWorkingHours, "SLA_WorkingDays", each 
        let
            wh = [WorkingHours]
        in
            if wh = null or wh <= 0 then 0 else Number.RoundUp(wh / 9),
        type number
    ),
    
    // Final cleanup
    FinalTable = Table.SelectColumns(AddSLADays, {"SR_NUMBER", "SR_CREATED", "SR_CLOSE_DATE", "WorkingHours", "SLA_WorkingDays"})
    
in
    FinalTable

This approach significantly improves your query. The function provides robust date adjustments, correctly finding the nearest valid working time even across long holiday weekends. It successfully handles all edge cases, such as tickets created before, after, or during non-working periods. By encapsulating the complex logic, your main query becomes much simpler and more readable. Finally, the SLA calculation is correct, using Number.RoundUp to properly match the CEILING logic from your working DAX formula.

Best regards,

DataNinja777  Thanks for you reply
Getting same result as before 

If you see screenshot ticket closed 5:37 which is non working hour. Result returning with error 

I am creating day difference betweek created and closed the tickets
My requirement is 
if ticket got created non working hours I should adjust the time to working hours
ex: if ticket create morning 7 it should adjust same day 8 am (my working hours are 8AM-5PM)
if any ticket created after 5PM should change to next day 8AM 
To calculate accurate result 
same if any ticket opend in weekends or holidays should refelct to next working day

Below is my Mcode which is not working for the above cases and result returning with error

let
// Load data
Source = Sql.Database("RV2IVWPBI1P\RAYAMSSQLSERVER", "RayaOperationsData"),
SR = Source{[Schema="dbo", Item="Fact_Siebel_ServiceRequest"]}[Data],

// Remove unwanted columns
RemovedCols = Table.RemoveColumns(SR, {
"SR_TYPE", "SUB_AREA", "PRIORITY", "REQUESTOR_NAME_EN", "SEVERITY",
"SR_DUE_DATE", "TOTAL_TIME", "VEHICLE_MAKE", "VEHICLE_MODEL", "MODEL_YEAR",
"PENDING_PAYMENT_START", "PENDING_PAYMENT_END", "LAST_UPD", "dw_created", "dw_last_upd"
}),

ChangedTypes = Table.TransformColumnTypes(RemovedCols, {
{"SR_CREATED", type datetime},
{"SR_CLOSE_DATE", type datetime}
}),

// Define working hours
WorkingStart = #time(8, 0, 0),
WorkingEnd = #time(17, 0, 0),

// 🟨 Holiday List
HolidayList = {
#date(2025, 3, 30), #date(2025, 3, 31), #date(2025, 4, 1), #date(2025, 4, 2),
#date(2025, 6, 5), #date(2025, 6, 8), #date(2025, 6, 9), #date(2025, 6, 10)
},

AdjustedStart = Table.AddColumn(ChangedTypes, "AdjustedStart", each
let
dt = [SR_CREATED],
defaultTime = #time(8, 0, 0),
workingEnd = #time(17, 0, 0),
safeDT = if dt = null then DateTime.LocalNow() else dt,
t = Time.From(safeDT),
d = Date.From(safeDT),
dow = Date.DayOfWeek(d, Day.Sunday),
isWeekend = dow = 5 or dow = 6, // Friday = 5, Saturday = 6 (Sunday = 0)
isHoliday = List.Contains(HolidayList, d),

nextWorkingDate =
List.First(
List.Select(
List.Dates(d + 1, 7, #duration(1,0,0,0)),
each
let wd = Date.DayOfWeek(_, Day.Sunday)
in (wd <> 5 and wd <> 6) and not List.Contains(HolidayList, _)
)
),

adjusted =
if isWeekend or isHoliday then
DateTime.From(nextWorkingDate & defaultTime)
else if t < defaultTime then
DateTime.From(d & defaultTime)
else if t >= workingEnd then
DateTime.From(nextWorkingDate & defaultTime)
else
safeDT

in
adjusted,
type datetime
),

// ✅ Adjust Closed Date
AdjustedEnd = Table.AddColumn(AdjustedStart, "AdjustedEnd", each
let
dt = if [SR_CLOSE_DATE] = null then DateTime.LocalNow() else [SR_CLOSE_DATE],
t = Time.From(dt),
d = Date.From(dt),
adjusted =
if t < WorkingStart then
DateTime.From(d + WorkingStart)
else if t > WorkingEnd then
DateTime.From(d + WorkingEnd)
else dt
in
adjusted
, type datetime),

// Date Range
AddDateRange = Table.AddColumn(AdjustedEnd, "DateRange", each
let
s = Date.From([AdjustedStart]),
e = Date.From([AdjustedEnd])
in
List.Dates(s, Duration.Days(e - s) + 1, #duration(1, 0, 0, 0))
, type list),

// Working Days (Sunday–Thursday, excluding holidays)
WorkingDates = Table.AddColumn(AddDateRange, "WorkingDates", each
List.Select([DateRange], each Date.DayOfWeek(_, Day.Sunday) <= 4 and not List.Contains(HolidayList, _))
, type list),

// Calculate Working Hours
WorkingHours = Table.AddColumn(WorkingDates, "WorkingHours", each
let
startDT = [AdjustedStart],
endDT = [AdjustedEnd],
startDate = Date.From(startDT),
endDate = Date.From(endDT),
dates = [WorkingDates],
hours = List.Sum(
List.Transform(dates, each
let
currentDate = _,
startTime = if currentDate = startDate then Time.From(startDT) else WorkingStart,
endTime = if currentDate = endDate then Time.From(endDT) else WorkingEnd,
validStart = if startTime < WorkingStart then WorkingStart else startTime,
validEnd = if endTime > WorkingEnd then WorkingEnd else endTime,
duration = Duration.TotalHours(validEnd - validStart)
in
if validEnd > validStart then duration else 0
)
)
in
if startDT = null or endDT = null then null else Number.Round(hours, 2)
, type number),

// Calculate SLA Working Days (9 hours per day rule)
SLA_WorkingDays = Table.AddColumn(WorkingHours, "SLA_WorkingDays", each
let
wh = [WorkingHours]
in
if wh = null then null
else if wh < 9 then 0
else Number.RoundUp(wh / 9)
, type number),

Cleanup = Table.RemoveColumns(SLA_WorkingDays, {"DateRange", "WorkingDates"})
in
Cleanup