Solved: Calculating hours worked from a Clock In/Out log

Mike_Pap · 08-16-2020 12:51 AM

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

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.

View solution in original post

LeventK

You can use TOTALHOURS() expression directly

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

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