[RESOLVED] Return HH:MM from Number of Seconds

[Hassan Basri]

Hello everybody,

I have a function that returns HH:MM:SS from a number of seconds. I want the function to instead return HH:MM. I know I can just round up the minutes if seconds are more than zero, and then if the minutes become 00 I round up the hours. However I was wondering if there was a more elegant solution. Thank you.

Here is my HH:MM:SS function

Private Function TimeBetweenSeconds(TimeBegin As Variant, TimeEnd As Variant) As String

Dim intTimeOne As Double
Dim intTimeTwo As Double
Dim intDifference As Double

On Error GoTo ErrorHandler

intTimeOne = hour(TimeBegin) * 60 + minute(TimeBegin) + second(TimeBegin) / 60
intTimeTwo = hour(TimeEnd) * 60 + minute(TimeEnd) + second(TimeEnd) / 60

intDifference = intTimeTwo - intTimeOne

If intDifference < 0 Then
    intDifference = 1440 + intDifference
End If

TimeBetweenSeconds = Format$(CStr(Int(intDifference / 60)) & ":" & CStr(Int(((intDifference / 60) - Int(intDifference / 60)) * 60)) & ":" & round((intDifference - Int(intDifference)) * 60), "H:MM:SS")

Exit Function
ErrorHandler:
TimeBetweenSeconds = ""

End Function

[Arnoutdv]

Do you really want rounding?
Normally, for example when using the internal date/time functions, the seconds are stripped.

If you have the number of seconds you can divide them by 86400 to get the internal date/time

Debug.Print Format(43200/86400,"HH:MM:SS")
Debug.Print Format(43220/86400,"HH:MM:SS")

Debug.Print Format(43200/86400,"HH:MM")
Debug.Print Format(43220/86400,"HH:MM")

[Hassan Basri]

Do you really want rounding?
Normally, for example when using the internal date/time functions, the seconds are stripped.

If you have the number of seconds you can divide them by 86400 to get the internal date/time

Debug.Print Format(43200/86400,"HH:MM:SS")
Debug.Print Format(43220/86400,"HH:MM:SS")

Debug.Print Format(43200/86400,"HH:MM")
Debug.Print Format(43220/86400,"HH:MM")

Thanks for the reply. I have a countdown timer. Basically

If the time remaining is 4:33:22 I want to display 4:34
If the time remaining is 4:33:00 I want to display 4:33
if the time remaining is 4:32:54 I want to display 4:33
etc...

Thanks.

[Arnoutdv]

That's odd :-)

Then just add 60 to the value if value / 60 gives a remainder <> 0

[Elroy]

Why not add 30, and then just strip off the printing of seconds. That would seem to "round" to minutes, although I agree: We usually truncate in these situations.

[passel]

Given seconds, I would usually just add 59 and then do a Mod 60. This will round everything from 1 to 59 seconds up to the next minute, but not round up 0 seconds so should do what you want without having to do a divide by 60 and check for a remainder and add 60. Not much different than what Arnoutdv said, but removes the If condition so is a one liner.

[passel]

Why not add 30, and then just strip off the printing of seconds. That would seem to "round" to minutes, although I agree: We usually truncate in these situations.

For a countdown timer rounding down looks odd when you have 29 seconds or more remaining, so you would be showing 00:00 hours and minutes left for around 30 seconds. It looks a bit better if you show you have 00:01 left and only show 00:00 when it actually expires.

[Hassan Basri]

Given seconds, I would usually just add 59 and then do a Mod 60. This will round everything from 1 to 59 seconds up to the next minute, but not round up 0 seconds so should do what you want without having to do a divide by 60 and check for a remainder and add 60. Not much different than what Arnoutdv said, but removes the If condition so is a one liner.

Thanks passel. This is the solution I took which works great.

Thanks also to Arnoutdv and Elroy for your contributions.