Calculate working hours per day with check in and check out time

[ThuJa23]

Hi all, 

I have working hours data for 1 employee as below

with TerminalereignistypID is 1: check in, and 2: check out

The target working hours per day is 8:45

The break time is always fixed 45 minutes

I would like to calculate on the same day, how many hours an employee works by taking time from TerminalereignistypID  2 minus TerminalereignistypID  1 and minus also the fixed break time (0,45)

There are also some days, that the employee check out in the middle of day, and check in again as example from date 16.11.2022, if the break time between check out and check in again is greater than (>) 45 mins, then no more minus 0,45, but if it less than 45 mins, then minus also the rest, for ex: if self - break time is 30 mins, so in calculation of working hours just needs to minus more 15 mins (to reach total 45mins of break time).

In the example of 16.11.2022, from first check out to check in again took 1,50, it overs than 0,45, so in this case just calculate the sum of slot 1 working time (6:33 - 11:34) 5,01 and slot 2 (13:25 - 15:40) 2,15, sum of working hours this day is 7,16 (without minus 45 mins break time)

Please help me how should I do the DAX measure in this case to calculate the working hours rightly?

Thanks so much for your helps

Thu

[v-jianboli-msft · Accepted solution]

Hi @ThuJa23 ,

Based on your description, I have created a simple sample:

Please try:

Measure =
VAR _a =
    SUMX (
        'Table',
        IF (
            [TerminalereignistypID] = 2,
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        'Table',
                        [TerminalereignistypID] = 1
                            && [Time] < EARLIER ( 'Table'[Time] )
                            && [Date] = EARLIER ( 'Table'[Date] )
                    )
                ),
                [Time],
                MINUTE
            )
        )
    )
VAR _b =
    SUMX (
        'Table',
        IF (
            [TerminalereignistypID] = 1,
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        'Table',
                        [Date] = EARLIER ( 'Table'[Date] )
                            && [TerminalereignistypID] = 2
                            && [Time] < EARLIER ( 'Table'[Time] )
                    )
                ),
                [Time],
                MINUTE
            )
        )
    )
VAR _c =
    IF ( _b < 45, 45, 0 )
VAR _d = ( _a - _c ) / 60
RETURN
    INT ( _d )
        + 0.01
            * MOD ( _a - _c, 60 )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThuJa23]

Hi @v-jianboli-msft ,

So sorry for my late replies. 

It works well, just we have more conditions in breaking time for employees. 

For example:

- if the employees work more or equal than 6 hours, then just minus 30 mins for lunch break

- if the employees work more than 9 hours, then minus 45 mins (included breakfast break 15mins, and lunch break 45 mins)

I am trying to test more with the calculation based on your formular. 

But thanks so much for your helps, it helps me to understand the structure defintely better 

Best regards,

Thu