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,