# SSRS Calculating and Converting Time with Expression

Recently I came across a challenging issue with calculating a time span within a group inside an SSRS Report.  I am working on an issue where I need to be able to calculate labor pace operations per hour, and the time a person works on a particular labor task.  My report data looks like the following:

 Employee No Time Weight 240 07:34:21 13.805 07:56:09 13.505 08:24:03 14.890 09:05:17 9.975 09:46:51 13.990 09:48:47 11.560 10:14:31 11.510 10:44:13 13.325 11:07:37 10.245 12:09:19 14.765 12:20:58 13.170 12:42:54 15.735 13:00:49 15.305 13:18:46 16.865 13:28:50 15.365

As you can see, this person submitted their first weight at 7:34 AM with a weight of 13.805 lbs.  In SSRS, it’s fairly straightforward to have a total for the weight, however, a total for the shift proved to be a little more challenging.  I was hoping I could simply use the SUM() function in hopes that SSRS would know how to handle the time format and just give me a total time.  No such luck.  Through a series of trials and errors with the TimeSpan function, I realized that I could use the Min() and Max() functions with a simple math equation.  Ultimately, my formula wound up being the following:

=(Max(Fields!Time.Value)-Min(Fields!Time.Value))

when applying the function, it yielded the a total of:

05:54:29

My next challenge was formatting the total so that it could be used in an equation with the total weight in order to determine my average lbs per hour.  After several hours of trial and error with various TimeSpan and DateDiff/DatePart functions, I found simple was best and ended with this formula to get my results:

=(Sum(Fields!Weight.Value))/(((DatePart(“h”,(Max(Fields!Time.Value)))*60+DatePart(“n”,(Max(Fields!Time.Value))))-(DatePart(“h”,(Min(Fields!Time.Value)))*60+DatePart(“n”,(Min(Fields!Time.Value)))))/60)

this got me  an average of 34.58 lbs/hr and was able to use the formula throughout the report.