Calculate overtime per day based on multiple rows

AppSheet AppSheet Q&A
,
1

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

2

You can try

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

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.

4

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"
1 Like
5

How to multiply duration by local currency value?