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