Night shift (logic, calculation)
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.
