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