Calculate time between 8 - 5PM

[ReciMixi]

I am running to an issue that I can seem to calculate the time correctly. My goal is to show all the hours between 8AM to 5PM (working hours). I have a log in time and a log out time. I would like to return the out but showing only the working hours period, I have figure out for the morning, if login and logout time is before 8AM it will be BLANK. And if login time is before 8AM but logout time 8:10, that’s mean they have been working for 10 minutes. I have fixed the issue but for hours after 5PM, I couldn’t get my data to come out right.

Example: Hudson Login Time is 4:41:43 and Logout at 6:03:36 PM.

• The Total Available Time is 1:21:53 for that day.
• Total Available Time between 8 – 5PM should be 00:18:17 and not 23:41:43.

Code:

Available Time =

CALCULATE(

    SUMX(

        FILTER('BDA', 'BDA'[Status] = "Available"),

        'BDA'[Logout Time] - 'BDA'[Login Time]

    )

)

Available Time (8AM to 5PM) =

VAR TotalAvailableTime =

    SUMX(

        FILTER('BDA', 'BDA'[Status] = "Available"),

        IF(

            HOUR('BDA'[LogIn Time]) < 8 && HOUR('BDA'[Logout Time]) < 8,

            BLANK(),  -- Leave blank if both times are before 8 AM

            IF(

                HOUR('BDA'[LogIn Time]) >= 17 && HOUR('BDA'[Logout Time]) >= 17,

                BLANK(),  -- Leave blank if both times are after 5 PM

                IF(

                    HOUR('BDA'[LogIn Time]) < 8,  -- Calculate if LogIn Time is before 8 AM

                    IF(

                        'BDA'[Logout Time] >= TIME(8, 0, 0),

                        'BDA'[Logout Time] - TIME(8, 0, 0),

                        BLANK()

                    ),

                    IF(

                        HOUR('BDA'[Logout Time]) > 17,

                        TIME(17, 0, 0) - 'BDA'[LogIn Time],

                        IF(

                            HOUR('BDA'[LogIn Time]) >= 8 && HOUR('BDA'[Logout Time]) <= 17,  -- Calculate the time between 8 AM and 5 PM

                            'BDA'[Logout Time] - 'BDA'[LogIn Time],

                            BLANK()

                        )

                    )

                )

            )

        )

    )

RETURN

    FORMAT(TotalAvailableTime, "hh:mm:ss")

[Daryl-Lynch-Bzy]

Hi @ReciMixi , could you try something like the following lines:

Available Time (8AM - 5PM) =
CALCULATE(
    SUMX(
        FILTER('BDA', 'BDA'[Status] = "Available"),
        MAX( 'BDA'[Logout Time] , TIME( 8, 0 , 0) ) 
        - MIN( 'BDA'[Login Time] , TIME ( 17, 0, 0 ) )
    )
) 

You may need to modify this if you Login Time is a DateTime value.  It is best practice to separate Date and Time in separate columns in your data model.  Another consideration is whether the login time starts and end in the previous/next day.

[ReciMixi]

The original format is Date and time but I change the format to Time (hh:mm:ss AM/PM). Should I keep it or seperate the time and date. And the log in and out time. Usually will be in the same date for each agent.