Forum Discussion

ReciMixi's avatar
ReciMixi
Frequent Visitor
2 years ago

Calculate time between 8 - 5PM

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...
need helps
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ReciMixi
    Frequent Visitor
    2 years ago

    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.