Working Hours including Weekends but with different times

Hi All,

Wondering for some guidance here, I have searched Google far and wide and look in forums and I can't find anything to fully help me complete what I'm after so wondering if anyone on here can help and guide me.

I have currently have data pulled from Sharepoint for Start Times and End Times, I've been asked to calcualte how long it takes for an task to be completed but only during business hours, this I can find easily on youtube and google but the complex part is including weekends and bank holidays as we still operate then but on a small time frame.

I've got some code to work no problem with 8am - 8pm every day but this wouldn't work for what is needed so created some new code but this doesn't work as intended either so wondering if someone can just pick it apart and tell whats wrong?

I have a query table set up with the data below which has the working hours for each day (0 = Monday)

Removing the not needed data, this is then broken apart for Date Start / Finish and Time Start / Finish but each time I get the calculate of 0:00 if done on the same day or 12:00 if over 1 day.

This is the code below I have used to try and resolve the issue, I found the code from another site but doesn't work at all how I wanted it to and tried lots of tweaks but never changes the outcome required.

Working Hours Between Dates = 
var startDate = 'Hub SOS Raw'[DateCaseStarted]
var startTime = 'Hub SOS Raw'[TimeCaseStarted] - startDate
var endDate = 'Hub SOS Raw'[DateCaseComplete]
var endTime = 'Hub SOS Raw'[TimeCaseComplete] - endDate

var firstFullDay = startDate + 1
var lastFullDay = endDate - 1

var inBetweenWorkingHours = 
    IF(
        firstFullDay > lastFullDay,
        0,
        SUMX(CALENDAR(firstFullDay, lastFullDay), LOOKUPVALUE(WorkingHoursTable[WorkingHoursInAllDay], WorkingHoursTable[WeekDay], WEEKDAY('Hub SOS Raw'[DateCaseStarted], 2)))
    )

var firstDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))
var firstDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))

var lastDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))
var lastDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))

var effectiveStartTime = IF(startTime < firstDayStart, firstDayStart, startTime)
var effectiveEndTime = IF(endTime > lastDayEnd, lastDayEnd, endTime)
        
return 
    IF(
        startDate = endDate,
        24 * IF(effectiveEndTime > effectiveStartTime, effectiveEndTime - effectiveStartTime, 0),
        var firstDayWorkingHour = 
            24 *
            IF(
                startTime > firstDayEnd,
                0,
                firstDayEnd - effectiveStartTime
            )

        var lastDayWorkingHour = 
            24 *
            IF(
                endTime < lastDayStart,
                0,
                effectiveEndTime - lastDayStart
            )
        
        return firstDayWorkingHour + lastDayWorkingHour + inBetweenWorkingHours
    )