Forum Discussion
How to calculate overtime
Good morning people !
I have a spreadsheet with days and hours worked, it has the same day in different lines, and I need to calculate the hours exceeding 8 hours.
How can I do this ?
| User Name | Date | Interval Start | Interval End | Total Hours Worked |
| anonymous | 12/04/2022 | 07:28:09 | 12:01:00 | 04:32:51 |
| anonymous | 12/04/2022 | 13:00:26 | 16:31:08 | 03:30:42 |
| anonymous | 13/04/2022 | 07:51:33 | 12:01:00 | 04:09:27 |
| anonymous | 13/04/2022 | 12:53:54 | 14:19:23 | 01:25:29 |
| anonymous | 13/04/2022 | 14:19:29 | 16:59:26 | 02:39:57 |
Can anybody help me.
Thanks
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.
It is for creating a new column.
Hours exceeding 8 hours CC = VAR currentend = Data[Interval End] VAR currentdate = Data[Date] VAR workinghours = FORMAT ( SUMX ( FILTER ( Data, Data[Interval End] <= currentend && Data[Date] = currentdate ), Data[Interval End] - Data[Interval Start] ), "hh:mm:ss" ) RETURN IF ( workinghours <= "08:00:00", BLANK (), FORMAT ( workinghours - 1 / 3, "hh:mm:ss" ) )
2 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.
It is for creating a new column.
Hours exceeding 8 hours CC = VAR currentend = Data[Interval End] VAR currentdate = Data[Date] VAR workinghours = FORMAT ( SUMX ( FILTER ( Data, Data[Interval End] <= currentend && Data[Date] = currentdate ), Data[Interval End] - Data[Interval Start] ), "hh:mm:ss" ) RETURN IF ( workinghours <= "08:00:00", BLANK (), FORMAT ( workinghours - 1 / 3, "hh:mm:ss" ) )