Calculating Work Hours from Raw Log

[Qiro]

I have a sheet that logs whenever a person clocks in or out by creating a new line. This is ideal from a data visibility point of view, as each action can be logged and called as and when they're needed, however, it makes calculating time worked difficult because the log is in chronological order.

I CAN change this, but it means a rebuild of a fairly complex (for my level of understanding) app:

There are multiple dependencies on this method of logging clock in/outs. The simplest fix would be to find a way for Sheets or Appsheet to search for a 'clock in' for Tom and search down for a 'clock out' on the same date, then calculate the difference.

My problem is I have no idea how I would go about doing that. Has anyone got any suggestions?

[TeeSee1 · Accepted solution]

Suppose you  have a LOGS table like this

You can calculate work hours (type Duration) with an expression like this.

[date_time] -
ANY(SELECT(LOGS[date_time],
 AND([user]=[_THISROW].[user],DATE([date_time])=DATE([_THISROW].[date_time]),[in_out]="in")
))

I have created an action to set [work_hours] value that only gets triggered when [in_out] is "out* and assigned it to the FORM's event action.

You could create a bot or do mass update. It is up to you, I suppose.

[Qiro]

A great solution, thank you!
Having taken a few minutes to understand what we're doing here, the expression does:
Take the DATETIME right now, then subtract anything in the date_time column that matches the current user's name, today's date, and is a clock "in".

It would have taken me forever to come up with this myself, thanks again.