Totaling H:M:S in SSRS

Question

I have an expression that sums up the values of hours, minutes and seconds(HH:MM:SS) in Reporting Services with the expression:

    =CStr(sum(CInt(split(Fields!Column.Value,":")(0)))+sum(CInt(split(Fields!Column.Value,":")(1)))\60)
    &":"& CStr(sum(CInt(split(Fields!Column.Value,":")(1))) mod 60+sum(CInt(split(Fields!Column.Value,":")(2)))\60)  
    &":"& CStr(sum(CInt(split(Fields!Column.Value,":")(2))) mod 60)

It looked or worked fine until a user spotted that the minute is not being rounded up when we have a value of 60. For example, I have totals like so

    5:60:42, 7:60:02 and so on

How do I rectify this error in my expression so that the minute values are rounded up correctly when it get to 60?

Answer 1

I believe this should do the trick.

=CStr(sum(CInt(split(Fields!Column.Value,":")(0)))+(sum(CInt(split(Fields!Column.Value,":")(1))) + sum(CInt(split(Fields!Column.Value,":")(2)))\60)\60)
    &":"& CStr((sum(CInt(split(Fields!Column.Value,":")(1))) + sum(CInt(split(Fields!Column.Value,":")(2)))\60) mod 60)  
    &":"& CStr(sum(CInt(split(Fields!Column.Value,":")(2))) mod 60)

You must first calculate the total of all minutes before you take the mod 60.
I'll try and explain using pseudo code, this is for calculating the total minutes:

• Yours (wrong):

[ (sum_of_all_min mod 60) + (sum_of_all_sec \ 60) ]

• Correct approach:

[ sum_of_all_min + (sum_of_all_sec \ 60) ] mod 60

The same can then be applied when calculating the hours total, you must use the total minute amount before dividing it by 60.

• This is the hour calculation, only the corrected version:

sum_of_all_hours + [ sum_of_all_min + (sum_of_all_sec \ 60) ] \ 60

---

Example in numbers, imagine you got this:

sum_total_hours = 5
sum_total_min = 59
sum_total_sec = 75

• Using your example would give this:

5 + (59 \ 60) : (59 mod 60) + (75 \ 60) : (75 mod 60)
=> 5 + (0) : (59) + (1) : (15)
=> 05:60:15

• Correct approach

5 + [59 + (75 \ 60)] \ 60 : [59 + (75 \ 60)] mod 60 : (75 mod 60)
=> 5 + [59 + (1)] \ 60 : [59 + (1)] mod 60 : (15)
=> 5 + [60] \ 60 : [60] mod 60 : (15)
=> 06:00:15