Calculating hours worked from a Clock In/Out log

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!

You can use TOTALHOURS() expression directly


2 Likes

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?

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.

3 Likes

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

4 Likes