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,

In SharePoint Online list, it is not feasible to calculate the values in the same column. For your scenario, you may export the SharePoint list to Excel. And then filter users and use formulas(e.g. SUM function) in Excel to calculate the total hours for specific users.

Here is an article for your reference:

Math and trigonometry functions

Thanks,

Felix

Answer 3

The issue is that I cannot pull the data specific to one user and total all the hours for this user.  I can get the fields working correctly, I'm just not understanding how to query the data in a format that I can then add a field and formula to give me a global total of service hours.

Thank you,

Jeff

Answer 4

Formula for Calculated "Total Hours"   =TEXT([End Time]-[Start Time],"h:mm")

Excerpt of List showing fields with data. 

So...

How do I get to a place where I can query all the records added by a specific user and then add a field that totals all the "Total Hours" column for this specific user for end-of-year reporting and to show the user how many hours they've accrued at any given point in the school year?

-Query from another list?

-Export to Excel and create a formula that returns the data?

-Export to Access database and create a report that returns the data?

I'm banging my head on all of these options and there doesn't seem to be an obvious best practice.

....

I found this function reference https://support.office.com/en-us/article/VALUE-function-1FC46E51-4A25-4BA0-BCE2-512DD5D4D872 , but wouldn't you know it doesn't apply to SharePoint 2013...

VALUE function

Applies To: SharePoint Server 2010SharePoint Foundation 2010SharePoint Server 2007 Central AdministrationSharePoint Server 2007More...Less

Converts a text string that represents a number to a number.

Syntax

VALUE(text)

Text     is the text enclosed in quotation marks or a column reference containing the text you want to convert.

Answer 5

Hi JFraker,

Please check if the Start Date and End Date have values. If they have the same values or don't have values, and you use the formula =TEXT([End Date]-[Start Date],"h:mm"), the results would be 0:00.

 

Moreover, to clarify your scenario, please provide the following information:

1.Share a screenshot of the column types. To do that: In the SharePoint list, go to List settings>In the Columns section, capture a screenshot as below.

 

2.Find the calculated column in the Columns section, open it and capture a screenshot of the settings.

3.Share a screenshot of the issue symptom as shown in my first screenshot above.

Thanks,

Felix