Unanswered

Help with calculating hours worked through my Time Tracker app

(0) Share

Report

Posted on by vinidegrandi

I have followed @RandyHayes's guide on how to build a Time Tracker app thoroughly. I have customized it to my company's liking and it works wonders. But now I need to use its SharePoint list data to generate reports on how much time each employee worked every month. I'm having a hard time with it because every Activity Type is in the same column so it is not as simple as "column A - column B - 1 = hours worked". I have tried using Power BI, but haven't had any success. I know this video is 2 years old but there is this comment on it asking about this exact scenario, to which he says:

"In general though, you could just do a calculation on the final close and write that into the list."

How would I apply this to the app?

Thank you in advance for the answers.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

rubin_boer 4,841 Super User 2024 Season 1 on at

Like (0)

Report

hi @vinidegrandi can you give an example of what your data look like and what you want from it.

Was this reply helpful? Yes No
vinidegrandi 67 on at

Like (0)

Report

Yes, of course! So basically the data looks like this:

I've simplified the types into 1 (Check In), 2 (Lunch Out), 3 (Lunch In) and 4 (Check Out). What I need to calculate is all in the third column, where the time each activity was done is recorded. So in this picture he would've worked 8 hours total (18 - 9 - 1). I need to do this for the entire month for each employee.

Was this reply helpful? Yes No
rubin_boer 4,841 Super User 2024 Season 1 on at

Like (0)

Report
hi @vinidegrandi

you could use this as a base:

Group your data by date and email

Add a column to the hours work to the group

Like this:

data: colVini {activityType: , activityTime: , activityDate: , email:}

GroupBy(colVini, "activityDate", "email", "Per Day")

Then add a column. the four context variables correspond to your type

AddColumns(GroupBy(colVini, "activityDate", "email", "Per Day"), "Hours", With( { ctxCheckIn: LookUp('Per Day', activityType = 1).activityTime, ctxLunchOut: LookUp('Per Day', activityType = 2).activityTime, ctxLunchIn: LookUp('Per Day', activityType = 3).activityTime, ctxCheckOut: LookUp('Per Day', activityType = 4).activityTime }, DateDiff(ctxCheckIn, ctxLunchOut, Hours) + DateDiff(ctxLunchIn, ctxCheckOut, Hours) ) )

Result

Collection:

Gallery with Items = colVini

Let's group and add the columns as per the code above

So from data above

lets add another person (email)

Was this reply helpful? Yes No