Night shift (logic, calculation)

[Krivger]

Hi everybody,

I'm looking for a solution, but I can't find it. I would like to know the time worked.

Info:
- People work in the morning, afternoon and night. Overtime is also possible.

- Thousands of posts are made in a day, that's thousands of time data. So there is not only a beginning and an end.

- Morning shift: 06-14 (can be overtime: 02-14, 06-18)

- Afternoon shift: 14-22 (can be overtime: 10-22, 14-02)

- Night shift: 22-06 (can be overtime: 18-06, 22-10)

MIN - MAX works in the morning and in the afternoon, because the time data comes one after the other. It does not work at night because it is separate, e.g.: 0:00 (start) -> (end) 6:00 + 22:00 (start) -> (end) 0:00. This is a MIN - MAX 24h.

time worked = 
VAR balans =
    CALCULATE (
        MAX (EVENTHistory[hour]),
        FILTER (
            ALLSELECTED ( 'EVENTHistory' ),
            EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker]
        )
    )
        - CALCULATE (
            MIN (EVENTHistory[hour]),
            FILTER (
                ALLSELECTED ( 'EVENTHistory' ),
                EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker]
            )
        )
RETURN
    FORMAT ( INT ( balans * 24 ), "00" ) & ":"
        & FORMAT ( INT ( MINUTE ( balans ) ), "00" ) & ":"
        & FORMAT ( INT ( SECOND ( balans ) ), "00" )

Thank you in advance for your help.

[Daryl-Lynch-Bzy]

Hi @Krivger - part of your problem is that you are tying to calculate the difference based on TIME data type.  You need to consider changing to DATETIME data type.  Please consider the following example:

The Time Difference calculated column in the first row cannot calculate the difference because the Time does not recognise the change in date.  When the DateTime is captured, the time difference is correctly calculated.

[Krivger]

Hi.
Unfortunately, your answer is not good. Normally it would be good, but in this case it is not good.

Time has shifted. 

1 day = (example) 2022.10.16 6:00:00 -> 2022.10.17 6:00:00 = 24 hours

I tried to subtract 6 hours, but it wasn't good either. If I look at it for a week, it's even worse. I have no idea 😞