JQuery for Everyone: Total of Time Differences
Problem: Eric commented on my last article and [basically] issued another challenge.
Will this work for time values?
Solution: Same concept as adding money. Grab the values into an array. Use JavaScript and regular expressions to do some math and massage the data.

To see the formula explained and the code snippet, click “Read more.”
The formula:
=(TRUNC([End Time]-[Start Time],0)*24)+TEXT([End Time]-[Start Time],"h")&":"&RIGHT(TEXT([End Time]-[Start Time],"h:mm"),2)
End Time – Start Time returns days in decimal. We don’t need anything beyond the decimal point here, so I use TRUNC (,0) to remove it. Next, I add that value to a TEXT conversion of the date difference in hours, “h.” Last, I concatentated minutes. For some reason, you can’t do a TEXT conversion of minutes without hours so I used RIGHT to only take the minutes from that string.
The jQuery:
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script> <script type="text/javascript"> $(function() {//sums time in specific list column var col = 4; //which column to sum var arrayList = $("table.ms-listviewtable:first> tbody:eq(2)> tr").find(">td:eq("+col+")").get(); var hrs = 0; var min = 0; $.each(arrayList, function(i,e){ hrs += Number($(e).text().replace(/:[0-9]*/, "")); min += Number($(e).text().replace(/[0-9]*:/, "")); }); if (min > 59) { //convert minutes to hours var minA = 0; minA = Number((min / 60).toFixed(0)); hrs += minA; min -= minA * 60; } $("#aggr> tr:first> td:eq("+col+")") .html("<b>Total = "+hrs+" hours, "+min+" minutes</b>"); }); </script>
UPDATE: Grouped Data
This version works with groups. Note, it uses a newer version of jQuery. Although that should not affect the results, it was not tested with the previous version used in the above script.
<script type="text/javascript"> //?PageView=Shared&ToolPaneView=2 //console.time('timer'); //console.timeEnd('timer'); if(typeof jQuery=="undefined"){ var jQPath="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/"; document.write("<script src='",jQPath,"jquery.min.js' type='text/javascript'><\/script>"); } </script> <script type="text/javascript"> /* * Copyright (c) 2008 Paul Grenier (endusersharepoint.com) * Licensed under the MIT (MIT-LICENSE.txt) */ function sumTime(groupName) {//sums time in specific list column if (!groupName) return; var col = 5; //which column to sum var arrayList = $("#tbod"+groupName+"_> tr").find(">td:eq("+col+")").get(); var hrs = 0; var min = 0; $.each(arrayList, function(i,e){ hrs += Number($(e).text().replace(/:[0-9]*/, "")); min += Number($(e).text().replace(/[0-9]*:/, "")); }); if (min > 59) { //convert minutes to hours var minA = 0; minA = Number((min / 60).toFixed(0)); hrs += minA; min -= minA * 60; } $("#aggr"+groupName+"_> tr:first> td:eq("+col+")") .html("<b>Total = "+hrs+" hours, "+min+" minutes</b>"); } //rerwite grouping function as needed function ExpGroupRenderData(htmlToRender, groupName, isLoaded) { $("#tbod"+groupName+"_").attr("isloaded",isLoaded) .html(htmlToRender) .show("fast",sumTime(groupName)); } </script>
- JQuery for Everyone: Accordion Left Nav
- JQuery for Everyone: Print (Any) Web Part
- JQuery for Everyone: HTML Calculated Column
- JQuery for Everyone: Dressing-up Links Pt1
- JQuery for Everyone: Dressing-up Links Pt2
- JQuery for Everyone: Dressing-up Links Pt3
- JQuery for Everyone: Cleaning Windows Pt1
- JQuery for Everyone: Cleaning Windows Pt2
- JQuery for Everyone: Fixing the Gantt View
- JQuery for Everyone: Dynamically Sizing Excel Web Parts
- JQuery for Everyone: Manually Resizing Web Parts
- JQuery for Everyone: Total Calculated Columns
- JQuery for Everyone: Total of Time Differences
- JQuery for Everyone: Fixing Configured Web Part Height
- JQuery for Everyone: Expand/Collapse All Groups
- JQuery for Everyone: Preview Pane for Multiple Lists
- JQuery for Everyone: Preview Pane for Calendar View
- JQuery for Everyone: Degrading Dynamic Script Loader
- JQuery for Everyone: Force Checkout
- JQuery for Everyone: Replacing [Today]
- JQuery for Everyone: Whether They Want It Or Not
- JQuery for Everyone: Linking the Attachment Icon
- JQuery for Everyone: Aspect-Oriented Programming with jQuery
- JQuery for Everyone: AOP in Action - loadTip Gone Wild
- JQuery for Everyone: Wiki Outbound Links
- JQuery for Everyone: Collapse Text in List View
- JQuery for Everyone: AOP in Action - Clone List Header
- JQuery for Everyone: $.grep and calcHTML Revisited
- JQuery for Everyone: Evolution of the Preview
- JQuery for Everyone: Create a Client-Side Object Model
- JQuery for Everyone: Print (Any) Web Part(s) Plugin
- JQuery for Everyone: Minimal AOP and Elegant Modularity
- JQuery for Everyone: Cookies and Plugins
- JQuery for Everyone: Live Events vs. AOP
- JQuery for Everyone: Live Preview Pane
- JQuery for Everyone: Pre-populate Form Fields
- JQuery for Everyone: Get XML List Data with OWSSVR.DLL (RPC)
- Use Firebug in IE
- JQuery for Everyone: Extending OWS API for Calculated Columns
- JQuery for Everyone: Accordion Left-nav with Cookies Speed Test
- JQuery for Everyone: Email a List of People with OWS
- JQuery for Everyone: Faster than Document.Ready
- jQuery for Everyone: Collapse or Prepopulate Form Fields
- jQuery for Everyone: Hourly Summary Web Part
- jQuery for Everyone: "Read More..." On a Blog Site
- jQuery for Everyone: Slick Speed Test
- jQuery for Everyone: The SharePoint Game Changer
- JQuery For Everyone: Live LoadTip
Great! Can’t wait to use this.
Thanks Paul, this is great. Now to take it one step further, is there a way to see how old an item is based on the Created date? I found a calculation that showed the age in years, months and days, but what about hours and minutes?
I actually have this calculation worked out already but don’t have a use case yet. I was thinking of writing it up for the Discussion List, do you have another idea?
Is there a way to use this with groups? This is a perfect solution for me. I am using this on a calendar list where I can tally up the amount of hours someone is scheduled to work on a given week. Basically, I have a view of the calendar that groups together the employees and then has the total length of their shift. From there, I want to total up their hours scheduled to work so I can keep a record.
If that is possible with this, you would be a lifesaver!
@Mark,
The problem here is that it will only total groups that are visible since the data is not actually on the page until the group expands.
So, to remain consistent with the way group functions work normally, I’d recommend against using it with groups unless you only want to total by group.
I’ll update this post with a version that works for groups.
AutoSponge, I appreciate this very much! While the complete total is great, I need a version that totals the groups because there are several employees on the calendar that need their hours totaled up. Having it on one calendar makes it a better solution. Would this work any different from the Totals on Calculated groups jquery you created previously?
@Mark,
The second version (above) totals by group.
This works wonderfully! Your work is inspirational and really adds to the features available to Share Point!
Paul,
there is no such thing as “groupName” in my HTML when looking at the source code of a grouped list.
When adding your script the groups expand so i guess it does something but the column does not get calculated properly (no result at all) and I assume that this is due to the missing property.
Any ideas?
@Martin,
groupName comes from the JavaScript. SP passes a parameter called groupName, I use the same parameter in my script.
If your column did not calculate, chances are you have the col variable set to the wrong column or the column is not a time or time/date field.
You also need to have something else in the list counted or summed (using standard oob view features) to provide the aggregation row where we place the time sum.
@Paul.
Thanks a lot, it works as intended now.
Would it be possible to:
a. Add the totals for a two level nested grouping, for each grouping?
b. Add the totals for the entire view?
The reason I want to calculate for two levels is that I first group on the month of the start time for the list object and I then group on the actual project.
We need to present both the total amount of hours for each project and also for the entire month.
I would also like to calculate all the hours spent for this customer no matter month or project(the entire view).
I forgot one thing.
If we just use the “count” row as an placeholder for our calculation, shouldn’t we be able to hide the “count” text and just keep the “totals” text?
I took a quick look at your code but I’m not familiar with neither jQuery nor SharePoint code in order to figure out how to do it.
The more complicated this gets, the more likely you’re not going to be satisfied until you use something like my owsapi (http://owsapi.codeplex.com/) or a web service to fetch the data (or a custom web part).
Remember, this script only sees what was rendered to the page. Stuff in initially collapsed groups has not been rendered yet.
I have no problems using the OWS API. I’ll take a look at it asap.
Thanks for the help and all the great scripts. :)
For a start and end difference, I used the following formula:
=TEXT([Outage End]-[Outage Start],”h:mm”)
Seems to be working just fine if difference is under 24 hours.
Also I notice that if you set the “Group By” option:
By default, show groupings: Expanded
You will no longer have the total, you must use “Collapsed” I think because the total comes from the rendering of “expanding” the group, correct?
Hi,
Just stumbled across this excelent post, I would like to return the average time difference as opposed to the total as demonstrated in http://www.endusersharepoint.com/?p=1106 is this easy to do?
Thanks
Eoin