SSRS TimeFormat to 00:00:01

Question

I have been struggling with a time format issue in SSRS. What I have so far is that seconds are listed with 00:00:15 but minutes like 15:25 instead of 00:15:25. I’ve been playing with it for a while now, but somehow it won’t show, no matter where I put the "00:" &

This is my code

=IIF(Sum(Fields!ActualTime.Value)/Fields!EventCount.Value < 1,"", IIF((Sum(Fields!ActualTime.Value)/Fields!EventCount.Value)/60 < 1, "00:00:" & (Sum(Fields!ActualTime.Value)/Fields!EventCount.Value), IIF((Sum(Fields!ActualTime.Value)/Fields!EventCount.Value)/3600 >= 1, ((Sum(Fields!ActualTime.Value)/Fields!EventCount.Value) - (Sum(Fields!ActualTime.Value)/Fields!EventCount.Value) mod 3600)/3600 & ":","") & IIF((Sum(Fields!ActualTime.Value)/Fields!EventCount.Value) mod 3600 >= 1, ((Sum(Fields!ActualTime.Value)/Fields!EventCount.Value) mod 3600 - (Sum(Fields!ActualTime.Value)/Fields!EventCount.Value) mod 3600 mod 60)/60 & ":","") & (Sum(Fields!ActualTime.Value)/Fields!EventCount.Value) mod 3600 mod 60))

Also 1 second, minute or hour is listed without a leading 0. Like this: 01:01:01

Please advise. Thanks in advance.

score 0 · Answer 1 · answered Mar 24 '15 at 10:39

You can set the time format through the expression for a text fields. Click on the textbox and press f4. Go to the format and click on the dropdown and then click on expression.

In the expression u can write something like this.

=switch("More than one hour","HH:mm:ss",
  "less than one hour and more than a minute","MM:ss",
  "Less than one minute","SS",)

U have to put ur conditions in the part where I have mentioned the times ('more than one year etc.'). let me know if still ur facing any problems.

score 0 · Accepted Answer · edited May 23 '17 at 12:12

I couldnt get this to work. So after searching the web for a few days, i found a Function here on stackoverflow that converts seconds to hh:mm:ss. Since there where 16 fields that needed this calculation i found it easier to use this Function. Thanks for the answer anyways.

Public Function ConvertSecondsToHourMinSec(ByVal intTotalSeconds) As String
Dim hours As String = INT(intTotalSeconds / 3600)
If Len(hours) < 2 Then
hours = RIGHT(("0" & hours), 2)
End If
Dim mins As String = RIGHT("0" & INT((intTotalSeconds Mod 3600) / 60), 2)
Dim secs As String = Right("0" & INT((intTotalSeconds Mod 3600) Mod 60), 2)
ConvertSecondsToHourMinSec = hours & ":" & mins & ":" & secs
End Function

In the textbox check for < 1 to show nothing and run the custom function otherwise.

=IIF(Sum(Fields!DownTime.Value)/Fields!EventCount.Value < 1,"",Code.ConvertSecondsToHourMinSec(Sum(Fields!DownTime.Value)/Fields!EventCount.Value))

SSRS TimeFormat to 00:00:01

2 Answers2