Working Hours Between two date/time fields

Hello,

Seeking the assistance of the forum, I have two date/time fields in the following format and the following script to calculate the working hours between 8 - 1700 excluding weekend. The script is calculating same day correctly however, if the dates spans between multiple days the hours are incorrect as see below.

Can someone help me modify this script or provide a solution for me? I'm really struggling here to get the working hours.

Same day calculation

Mutiple days

Script

WorkingHoursDuration =
VAR StartDateTime = [date/time]
VAR EndDateTime = [date/time]

-- Define working hours
VAR StartOfWorkday = TIME(8, 0, 0)
VAR EndOfWorkday = TIME(17, 0, 0)

-- Helper function to calculate working hours in a given interval
VAR CalculateWorkingHoursInInterval =
VAR IntervalStart = MAX(DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday, StartDateTime)
VAR IntervalEnd = MIN(DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday, EndDateTime)
RETURN
IF (
IntervalEnd > IntervalStart,
DATEDIFF(IntervalStart, IntervalEnd, MINUTE) / 60.0,
0
)

-- Adjust start and end times if they fall outside working hours
VAR AdjustedStartTime =
IF (
WEEKDAY(StartDateTime, 2) > 5 || StartDateTime < DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday,
DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday,
IF (
StartDateTime > DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday,
DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday,
StartDateTime
)
)

VAR AdjustedEndTime =
IF (
WEEKDAY(EndDateTime, 2) > 5 || EndDateTime > DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + EndOfWorkday,
DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + EndOfWorkday,
IF (
EndDateTime < DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + StartOfWorkday,
DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + StartOfWorkday,
EndDateTime
)
)

-- Calculate working hours if the interval is within the same day
VAR SameDayHours =
IF (
DATEVALUE(AdjustedStartTime) = DATEVALUE(AdjustedEndTime),

0
)

-- Calculate working hours for full working days between start and end dates
VAR FullDaysStart = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + 1
VAR FullDaysEnd = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) - 1

VAR FullDaysHours =
IF (
FullDaysStart <= FullDaysEnd,
CALCULATE (
SUMX (
FILTER (
CALENDAR (FullDaysStart, FullDaysEnd),
WEEKDAY([Date], 2) <= 5
),
9
)
),
0
)

-- Calculate working hours for the start and end days
VAR StartDayHours =
IF (
DATEVALUE(AdjustedStartTime) < DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + EndOfWorkday,
VAR IntervalStart = MAX(StartOfWorkday, AdjustedStartTime)
VAR IntervalEnd = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + EndOfWorkday
RETURN
CalculateWorkingHoursInInterval,
0
)

VAR EndDayHours =
IF (
DATEVALUE(AdjustedEndTime) > DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) + StartOfWorkday,
VAR IntervalStart = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) + StartOfWorkday
VAR IntervalEnd = MIN(EndOfWorkday, AdjustedEndTime)
RETURN
CalculateWorkingHoursInInterval,
0
)

-- Total working hours
VAR TotalWorkingHours =
SameDayHours + FullDaysHours + StartDayHours + EndDayHours

RETURN
MAX(0, TotalWorkingHours)