Function: Time Format

[sjohnson1984]

Hello all,

Thanks for reading firstly.

I am looking to format seconds into a format similar to that of an Excel "custom" format [h]:mm:ss

I have crafted the following however I am getting an error:

Public Shared Function timeFormat(Seconds As Double) as String

if (Seconds = nothing) or (Seconds = 0)
     timeFormat = 0

 else
     Dim hours as Double = Seconds / 3600
     Dim minutes as Double = (Seconds % 3600) / 60
     Dim seconds as Double = Seconds % 60
     timeFormat = CStr(hours) & ":" & CStr(minutes) & ":" & CStr(seconds)

end if

End Function

Acknowledged that there is probably a real basic error in there, however I cannot get this to work.

I am using this as "custom code" in SQL Reporting Services 2005, Windows XP .NET 2.0

Can anybody push me in the right direction please!?

Thanks in advance?

J

[sjohnson1984]

OK I do know that I had two variables named "seconds", though I still cannot get this to work:

Public Shared Function timeFormat(Seconds As Double) as String

Dim outHours, outSeconds, outMinutes as Double

if (Seconds = nothing) or (Seconds = 0)
     timeFormat = 0

else
    outHours = Seconds / 3600
    outMinutes = (Seconds % 3600) / 60
    outSeconds = Seconds % 60
    timeFormat = CStr(outHours) & ":" & CStr(outMinutes) & ":" & CStr outSeconds)
    Return timeFormat
    timeFormat = seconds

end if

End Function

[Plater]

So you have a value in seconds, and you want to know how that time would be expressed in units of hours: minutes: seconds?

[code=vbnet]
Public Shared Function timeFormat(Seco nds As Double) as String
Dim outHours, outSeconds, outMinutes as Double
if (Seconds = nothing) or (Seconds = 0)
timeFormat = "0"
else
outHours = Seconds / 3600
Seconds=(Second s % 3600) 'take what is "left over"
outMinutes = (Seconds /60)
Seconds=(Second s % 60) 'take what is "left over"
outSeconds = Seconds
timeFormat = CStr(outHours) & ":" & CStr(outMinutes ) & ":" & CStr outSeconds)
end if
Return timeFormat
End Function
[/code]

Another possibility would be to use the built-in functions
[code=vbnet]
Public Shared Function timeFormat(Seco nds As Double) as String
Dim t as TimeSpan
t = TimeSpan.FromSe conds(Seconds)
timeformat = t.Hours & ":" & t.Minutes & ":" & t.Seconds
Return timeformat
End Function
[/code]

[sjohnson1984]

Thanks for your reply!

The bottom function works for single cells, however when summing, it does not display correctly. In the detail row I have:

=Fields!TotalDailySeconds.value

...in a hidden row I have

 =sum(Fields!TotalDailySeconds.value)

, a textbox which I have called ctlTotalTime.

Then in the row which is visible I have

=code.timeFormat(ReportItems!ctlTotalTime)

however, even going through this process means the time is not showing correctly e.g:

8:34:54 + 8:55:53 + 9:33:37 + 21:26:18 => 0:30:42

The actual TotalTime in seconds is 174642, so the minutes and seconds part is right however the hours obviously is not. Does anybody have any idea as to why this might be/ (I have checked the Format property!)

J

[Plater]

You should be summing the seconds before applying them to the function that returns them as an hour:minute:sec ond format.

I think the default format for TimeSpan.ToStri ng() is hh:MM:ss, but am not sure.

[sjohnson1984]

I am suprised no-one shouted at this one, however the function did not work correctly as TimeSpan moves any hours over 24 into the Day property!

The correct function is:

Public Shared Function timeFormat(Seconds As Double) as String
   Dim t as TimeSpan
   t = TimeSpan.FromSeconds(Seconds)
   timeformat = CStr((t.Days*24) + t.Hours) & ":" & t.Minutes & ":" & t.Seconds
   Return timeformat
End Function

...thought I would post this in order that anyone else in a similar spot does not spend a week, in between all the other projects, looking for the solution!

[Plater]

That was a good catch, didn't even see that when I whipped up the code.