Number as hh:mm i.e. as hours & minutes

[Guest]

I have a calculation that brings back a number which represents seconds. I
would like to convert that to hours / minutes / seconds, i.e. hh:nn:ss. If I
just apply the format it returns 00:00:00.

Any ideas?

 

[Rick B]

Here is an example that will do hours and minutes. You can modify it to
meet your needs.


Displaying minutes as Hours:Minutes (X:XX)
=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")

 

[Guest]

How would you do it for Hours:Minutes:Seconds ?

Here is an example that will do hours and minutes. You can modify it to
meet your needs.


Displaying minutes as Hours:Minutes (X:XX)
=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")


--
Rick B

I have a calculation that brings back a number which represents seconds. I
would like to convert that to hours / minutes / seconds, i.e. hh:nn:ss. If
I
just apply the format it returns 00:00:00.

Any ideas?

 

[John Spencer]

If the number of seconds does NOT exceed the number of second in a day. You
can use the DateAdd function.

DateAdd("s",[YourSecondsValue],0)

If you need the specific format, then apply the format against the value
returned by the above.

If the value exceeds 24 hours (86,400 seconds) then you will have to use
some math and string functions

[YourSecondsValue] Mod 60 returns the number of seconds
[YourSecondsValue]\3600 returns the number of hours
([YourSecondsValue] \60) Mod 60 returns the number of minutes

So you can combine those into one expression as
[YourSecondsValue]\3600 & ":" & Format(([YourSecondsValue] \60) Mod 60,"00")
& ":" & Format(YourSecondsValue] Mod 60,"00")

 

[Rick B]

I guess you would first divide by the number of seconds in an hour, then the
number of seconds in a minute, then the remainder.

I got you started. Play around with it.


--
Rick B

How would you do it for Hours:Minutes:Seconds ?

Here is an example that will do hours and minutes. You can modify it to
meet your needs.


Displaying minutes as Hours:Minutes (X:XX)
=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")


--
Rick B

I have a calculation that brings back a number which represents seconds.
I
would like to convert that to hours / minutes / seconds, i.e. hh:nn:ss.
If
I
just apply the format it returns 00:00:00.

Any ideas?

 

[Douglas J. Steele]

As long as the number of seconds is less than 86400 (the number of seconds
in a day), you can divide the number of seconds by 86400 and use the format
function on it:

Format(NumberOfSeconds/86400&, "hh:nn:ss")

(the & after 86400 is to ensure that it's treated as a Long Integer, since
86400 is large than an Integer can hold)

 

[Guest]

thanks for your help - just the trick

If the number of seconds does NOT exceed the number of second in a day. You
can use the DateAdd function.

DateAdd("s",[YourSecondsValue],0)

If you need the specific format, then apply the format against the value
returned by the above.

If the value exceeds 24 hours (86,400 seconds) then you will have to use
some math and string functions

[YourSecondsValue] Mod 60 returns the number of seconds
[YourSecondsValue]\3600 returns the number of hours
([YourSecondsValue] \60) Mod 60 returns the number of minutes

So you can combine those into one expression as
[YourSecondsValue]\3600 & ":" & Format(([YourSecondsValue] \60) Mod 60,"00")
& ":" & Format(YourSecondsValue] Mod 60,"00")

I have a calculation that brings back a number which represents seconds. I
would like to convert that to hours / minutes / seconds, i.e. hh:nn:ss. If
I
just apply the format it returns 00:00:00.

Any ideas?