LinkedIn
Twitter
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?
Solved
1 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.
| User | Count |
|---|---|
| 18 | |
| 15 | |
| 10 | |
| 7 | |
| 4 |
