Calculating hours worked from a Clock In/Out log

[Mike_Pap]

Hi there, my client asked for a Clock In/Out feature which i created. Each one of those buttons records the user, the type (clock in or clock out) and the timestamp of when it was clicked. Now, the client wants to be able to create a weekly timesheet report for each user by clicking on an action. I’ve setup the Weekly Timesheet slice but i’'m having trouble figuring out the expression for calculating the time each user has spent working throughout the week. How can I retrieve the Clock In/Out pairs so that i can subtract the Clock out from the Clock In time & then sum that up to get the total?

Thanks in advance!

[LeventK]

You can use TOTALHOURS() expression directly

help.appsheet.com

TOTALHOURS()

Count of hours in Duration

help.appsheet.com

TOTALMINUTES()

Count of minutes in Duration

[Mike_Pap]

Thanks for the tip! The log though is saving the timestamp of when the button is clicked and a user may clock in/out multiple times in one day. How would I go about iterating over every Clock In/Out pair in the weekly log?

Should i have another table which as soon as the clock out button is clicked after the clock in one, it calculates the difference and adds it to that new table?

[LeventK · Accepted solution]

Create a Virtual Column which will eventually calculate the durations for each of the clock-in&out records of each user:

---

CALCULATE DURATIONS Duration Type i.e. [Duration]

---

[Clock-Out] - [Clock-In]

---

CALCULATE TOTAL HOURS Decimal Type

---

TOTALHOURS(
    SUM(
        SELECT(
            TableName[Duration],
            [UserID] = [_THISROW].[UserID]
        )
    )
)

Make necessary changes to adopt the actual table & column names of your schema.

[Rifad]

When i use this its returning negative values when its Next day. How can i tackle that issue. Some one clocks in at 11PM and clocks out at at 12.30am or 2am… It returns a negative value. Looking for your suggestions on this.

[Karthik1]

If you have already found solution to the issue pls help. I am having same issue.

[Aleksi]

Check a sample called “In Duty” from https://www.appsheet.com/portfolio/531778