1,491 articles and 10,269 comments as of Tuesday, April 13th, 2010

Tuesday, January 6, 2009

JQuery for Everyone: Total of Time Differences

ProblemEric 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>



Paul Grenier

View all entries in this series: PaulGrenier-JQuery for Everyone»
Entries in this series:
  1. JQuery for Everyone: Accordion Left Nav
  2. JQuery for Everyone: Print (Any) Web Part
  3. JQuery for Everyone: HTML Calculated Column
  4. JQuery for Everyone: Dressing-up Links Pt1
  5. JQuery for Everyone: Dressing-up Links Pt2
  6. JQuery for Everyone: Dressing-up Links Pt3
  7. JQuery for Everyone: Cleaning Windows Pt1
  8. JQuery for Everyone: Cleaning Windows Pt2
  9. JQuery for Everyone: Fixing the Gantt View
  10. JQuery for Everyone: Dynamically Sizing Excel Web Parts
  11. JQuery for Everyone: Manually Resizing Web Parts
  12. JQuery for Everyone: Total Calculated Columns
  13. JQuery for Everyone: Total of Time Differences
  14. JQuery for Everyone: Fixing Configured Web Part Height
  15. JQuery for Everyone: Expand/Collapse All Groups
  16. JQuery for Everyone: Preview Pane for Multiple Lists
  17. JQuery for Everyone: Preview Pane for Calendar View
  18. JQuery for Everyone: Degrading Dynamic Script Loader
  19. JQuery for Everyone: Force Checkout
  20. JQuery for Everyone: Replacing [Today]
  21. JQuery for Everyone: Whether They Want It Or Not
  22. JQuery for Everyone: Linking the Attachment Icon
  23. JQuery for Everyone: Aspect-Oriented Programming with jQuery
  24. JQuery for Everyone: AOP in Action - loadTip Gone Wild
  25. JQuery for Everyone: Wiki Outbound Links
  26. JQuery for Everyone: Collapse Text in List View
  27. JQuery for Everyone: AOP in Action - Clone List Header
  28. JQuery for Everyone: $.grep and calcHTML Revisited
  29. JQuery for Everyone: Evolution of the Preview
  30. JQuery for Everyone: Create a Client-Side Object Model
  31. JQuery for Everyone: Print (Any) Web Part(s) Plugin
  32. JQuery for Everyone: Minimal AOP and Elegant Modularity
  33. JQuery for Everyone: Cookies and Plugins
  34. JQuery for Everyone: Live Events vs. AOP
  35. JQuery for Everyone: Live Preview Pane
  36. JQuery for Everyone: Pre-populate Form Fields
  37. JQuery for Everyone: Get XML List Data with OWSSVR.DLL (RPC)
  38. Use Firebug in IE
  39. JQuery for Everyone: Extending OWS API for Calculated Columns
  40. JQuery for Everyone: Accordion Left-nav with Cookies Speed Test
  41. JQuery for Everyone: Email a List of People with OWS
  42. JQuery for Everyone: Faster than Document.Ready
  43. jQuery for Everyone: Collapse or Prepopulate Form Fields
  44. jQuery for Everyone: Hourly Summary Web Part
  45. jQuery for Everyone: "Read More..." On a Blog Site
  46. jQuery for Everyone: Slick Speed Test
  47. jQuery for Everyone: The SharePoint Game Changer
  48. JQuery For Everyone: Live LoadTip
 

Please Join the Discussion

19 Responses to “JQuery for Everyone: Total of Time Differences”
  1. eric says:

    Great! Can’t wait to use this.

  2. Manny says:

    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?

  3. AutoSponge says:

    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?

  4. Mark says:

    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!

  5. AutoSponge says:

    @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.

  6. AutoSponge says:

    I’ll update this post with a version that works for groups.

  7. Mark says:

    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?

  8. AutoSponge says:

    @Mark,

    The second version (above) totals by group.

  9. Mark says:

    This works wonderfully! Your work is inspirational and really adds to the features available to Share Point!

  10. Martin Edeliusd says:

    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?

  11. AutoSponge says:

    @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.

  12. Martin Edelius says:

    @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).

  13. Martin Edelius says:

    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.

  14. AutoSponge says:

    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.

  15. Martin Edelius says:

    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. :)

  16. Ken says:

    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.

  17. Ken says:

    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?

  18. Eoin says:

    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

Trackbacks

Check out what others are saying about this post...
  1. OneOfSix says:

    Add Links to SharePoint Wiki Toolbar using jQuery…




Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!