Forum Discussion

jbrines's avatar
jbrines
Icon for Advocate III rankAdvocate III
7 months ago
Solved

Help working out actual time which includes working hours

Hi Guys,

 

I got help on here to create this code, the issue is that it doesn't take working hours in to consideration, can the code be ammended to take these hours in to consideration.

 

Working hours

Mon - 8am - 5pm

Tue - 8am - 5pm

Wed 8am - 5pm

Thurs 8am - 5pm

Fri 8am - 3:30pm

 

Code

 

Actual Time = 
VAR StartDT = SELECTEDVALUE ( 'Results Data'[Created Date] )
VAR EndDT   = SELECTEDVALUE ( 'Results Data'[Closed Date] )

RETURN
IF (
    ISBLANK ( EndDT ) || EndDT < StartDT,
    BLANK(),
    
    VAR StartDate = INT ( StartDT )
    VAR EndDate   = INT ( EndDT )

    VAR SameDay = StartDate = EndDate

    -- Check working day
    VAR IsStartWorkingDay =
        WEEKDAY ( StartDate, 2 ) <= 5 &&
        NOT ( StartDate IN UKBankHols )

    VAR IsEndWorkingDay =
        WEEKDAY ( EndDate, 2 ) <= 5 &&
        NOT ( EndDate IN UKBankHols )

    -- Full working days between start and end
    VAR FullWorkingDays =
        MAX (
            0,
            NETWORKDAYS ( StartDate, EndDate, 1, UKBankHols )
                - IF ( IsStartWorkingDay, 1, 0 )
                - IF ( IsEndWorkingDay, 1, 0 )
        )

    -- Partial day contributions (calendar hours)
    VAR StartDayFraction =
        IF (
            IsStartWorkingDay,
            1 - ( StartDT - StartDate ),
            0
        )

    VAR EndDayFraction =
        IF (
            IsEndWorkingDay,
            EndDT - EndDate,
            0
        )

    VAR TotalCalendarDays =
        IF (
            SameDay,
            IF ( IsStartWorkingDay, EndDT - StartDT, 0 ),
            FullWorkingDays + StartDayFraction + EndDayFraction
        )

    RETURN
        MAX ( 0, TotalCalendarDays )
)