Solved: Calculate overtime per day based on multiple rows

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

TeeSee1

You can try

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

eddie61

Thank you @TeeSee1 for looking into my problem.

I get an error that it cannot find the column 'date' in [_THISROW].[date].

Maybe I need to clarify what I need.

Table with data (log table)

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
2	01.05.2022	04:00:00
2	01.05.2022	05:00:00

Table with the calculation (employee table)

employeeID	overtime (here I need the calculation / formula)
1	00:30:00
2	01:00:00

Does that make sense?

Edit: I also just realised that I probably need a Google Sheet Formula, because the rows in the employee table rarely get updated within the app, so the app formulas aren't computed.

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"

cleiton

How to multiply duration by local currency value?