Count Time Records in SharePoint List

(0) Share

Report

Posted on by StevenP

Hi All!

Happy new year first of all.

I have a SharePoint list called "Time Records", it uses a column called "VID" as the primary key, we have a bunch of users who log time against tasks and it stores the below data in the "Time Records" list.

"Start DateTime": 11/01/2021 10:00

"End DateTime": 11/01/2021 11:00

"VID": User().FullName

"TaskType": Choice Column (don't need to report on this)

I am trying to generate a report within PowerApps so that I can have a data table that'll show each unique VID (user) and then their total time logged between two date ranges.

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

FMontesRNX1 147 on at

Like (0)

Report

So the problem is when registering time and date?
OK, first, you need to consider every time you create a Sharepoint list item, will be recorded this 2 date records:
-Created (recommended)
-Modified
So you can use this fields to don't register date and time 'manually' each time, and use the Created field instead for all your report purposes

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report
Hi @StevenP ,

The below is syntax guidance (I cannot test it), but this is the structure I think you need.

With( { wList: Filter( 'Time Records', 'Start DateTime' >= YourStartDatePicker.SelectedDate && 'Start DateTime' <= YourEndDatePicker.SelectedDate ) }, AddColumns( GroupBy( wList, "VID", "Data" ), "TimeWorked", Sum( DatDiff( Data.'Start DateTime', Data.'End DateTime', Hours ) ) ) )

I suggest you display it in a Gallery (not a Data Table). I would also be advantageous to have a date (only) field as you will ahve to add one day to the end date you pick to compare with a date/time field.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
StevenP 65 on at

Like (0)

Report

Thanks for your response, I tried exactly how you sent it over (correcting the input names etc), but it doesn't seem to like the sum side?

Error2.png

Error1.png

Was this reply helpful? Yes No
StevenP 65 on at

Like (0)

Report

I had a re-think on my approach and thought what if I dump the calculated column (Date Diff) into a collection and then just sum the collection and have the value shown for each person on their user record.

I have dumped the Diff column into a collection depending on the Gallery selected user, is there a simple way for me to sum the column in order to get a total of 3 hours? I've tried the usual, but it just comes back with Blank value.

Thanks!

Diff.png

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report
Hi @StevenP ,

A bit more complex, but this is valid syntax

With( { wList: With( { wDates: Filter( 'Time Records', 'Start DateTime' >= YourStartDatePicker.SelectedDate && 'End DateTime' <= YourEndDatePicker.SelectedDate ) }, AddColumns( wDates, "Hours", DateDiff( 'Start DateTime', 'End DateTime', Hours ) ) ) }, AddColumns( GroupBy( wList, "VID", "Data" ), "TimeWorked", Sum( Data, Hours ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
StevenP 65 on at

Like (0)

Report

Thanks, this works perfectly, beyond grateful!

Was this reply helpful? Yes No