Solved: Calculate overtime per day based on multiple rows - Page 2

eddie61 · 05-02-2022 02:38 AM

Hi all

I'm trying to calculate the total overtime an employee has. Everything above 8 hours per day is overtime. My problem is, that an employee can have more than one entry (row) per day.

Example:

employeeID	date	duration
1	01.05.2022	04:00:00
1	01.05.2022	03:00:00
1	01.05.2022	02:30:00
1	02.05.2022	06:00:00
1	02.05.2022	01:00:00

I need a formula that calculates the "01.05.2022" as "01:30:00" overtime and the "02.05.2022" as "-01:00:00" overtime. It should result in giving me back an overall overtime value as "00:30:00".

Is this possible to do with an app formula or google sheet formula?

Thanks in advance.
Adrian

Rafael_ANEIC-PY

Hello @eddie61, you're getting that error when using @TeeSee1's solution because he assumed that you would have a DATE column in the table where you perform the calculation, which is expected, otherwise you wouldn't be able to make this calculation for different dates.

In case you actually don't need that, here is the expression without the date condition:

SUM(
 SELECT(your table[duration],[empId]=[_THISROW].[empId])
)
-
"008:00:00"

View solution in original post