Sharepoint list function

Question

Good morning, I'm tired of searching for a way to calculate a total time from user records in a SharePoint list.  I'm building a SharePoint list to track student volunteer hours.  Students have a unique ID number and I want to filter the list by this number and then see global hours for all of their community service.

The list calculates the Total Hours for each record entered, but I'm not able to get the Global Hours for a user.  I've tried exporting to Excel, but I'm running into a wall there.  In order to get the Total Hours to calculate correctly based on date/time entries for Start Time and End Time, I had to use the =TEXT function in the calculated field of the SharePoint list, so Excel formulas are all returning 0.00.

Anyone know of a better way to approach this?

Thank you

Answer 1

Configured a way that will work for us.  Still haven't figured out a way to show a student their Global hours in SharePoint, but we have a different database that they can see their hours.  I intend to explore programmatically creating SharePoint list views for each user that will return this data in the future.

For now:

SharePoint List field "Total Time" is a calculated number.  This formula changes the value from a time value to a numeric, 2-decimal number.

=ROUND((([End Time]-[Start Time])*24),2)

I then export the spreadsheet to Excel, and the community service manager will simply "Refresh All Connections" on the Data tab going forward.

Using SUMIFS, I'm separating Total Hours (J-column) for each user ID (A-column) and filtering for "Approved" (L-column).  The Approved, Pending and Rejected status is from the SharePoint list, and I'm using the same formula to return values for each type.

=SUMIFS(J7:J5006,A7:A5006,(B1),L7:L5006, "Approved")

Thanks for the help.

Answer 2

Hi JFraker,

Glad to hear that you find a solution and we appreciate your sharing. This will definitely benefit other community members who have the same requirement.

If you have any other problems when using Office 365, please feel free to post a new thread in the forum. We’re here to help you.

Regards,

Allan

Answer 3

Hi JFraker,

For this situation, you may follow the steps below to see the outcome.

1.In Excel, re-order the records using the Sort A to Z or Sort Z to A.

 

2.Filter the users, the records will be in sequence>Use SUM function and select the records to calculate them.

 

Thanks,

Felix

Answer 4

Thank you,

I'll keep working the Excel side.  It's quite easy to export the list to Excel.  The trouble I ran into was after filtering the list to only show a single user, the SUM function was still including rows that were hidden by the filter.  I'll go through it again and see if I get the same results.

Answer 5

Assuming there are too many students to create a view for each, try adding a total function on the id field and then filter the list by id. That should give you a unique total per user though you would have to find a way then to extract it.