convert a calculated value to Hours:Minutes:Seconds

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cadburys - 26 Oct 2005 10:34 GMT

I have searched the discussion board for the answer to my question but
unfortunately have not been able to apply any of the variations to my DB.

I have a field called LengthDF which is a number field and users enter the
duration in seconds of a Digital File into this field. (My ideal would be for
the user to be able to enter the duration in HH:MM:SS - but I realise that
for calculation purposes I will probably have to say that it has to be
entered in seconds or have a separate field for each - hours, minutes and
seconds.)

I have another field called DFLSum which calculates the total seconds e.g.
4316 seconds - which is 71 minutes and 56 seconds. my current formula is

=[DFLSum]\60 & Format([DFLSum] Mod 60,"\:00")

and shows 71:56 - but I need it to show 01:11:56

So my first question is can I use a single field to enter the duration in
hours, minutes and seconds and then have a total duration calculation showing
hours, minutes and seconds.

or must I have the user enter the duration in seconds - get a total seconds
caculation and then change the formatting to HH:MM:SS - if so how?

Any help or advise would be greatly appreciated!!!

Signature

Cheers, Cadburys

Reply to this Message

Douglas J Steele - 26 Oct 2005 11:50 GMT

Here's some air-code functions that should convert back and forth:

Function HHMMSStoSeconds(TimeIn As String) As Long

Dim varTimes As Variant

varTimes = Split(TimeIn, ":")
If IsNull(varTimes) Then
HHMMSStoSeconds = 0
Else
Select Case UBound(varTimes)
Case 0 ' Assume only seconds passed
HHMMSStoSeconds = CLng(varTimes(0))
Case 1 ' Assume mm:ss passed
HHMMSStoSeconds = 60 * CLng(varTimes(0)) + _
CLng(varTimes(1))
Case 2 ' Assume hh:mm:ss passed
HHMMSStoSeconds = 3600 * CLng(varTimes(0)) + _
60 * CLng(varTimes(1)) + CLng(varTimes(2))
Case Else
HHMMSStoSeconds = 0
End Select
End If

End Function

Function SecondsToHHMMSS(SecondsIn As Long) As String

Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngHours = SecondsIn \ 3600
lngMinutes = (SecondsIn \ 60) - lngHours * 60
lngSeconds = SecondsIn Mod 60

SecondsToHHMMSS = Format(lngHours, "0") & ":" & _
Format(lngMinutes, "00") & ":" & _
Format(lngSeconds, "00")

End Function

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I have searched the discussion board for the answer to my question but
> unfortunately have not been able to apply any of the variations to my DB.
[quoted text clipped - 21 lines]
>
> Any help or advise would be greatly appreciated!!!

Reply to this Message

Ofer - 26 Oct 2005 11:57 GMT

Try this
int((8316/60)/60) & format(int(4316/60) mod 60,"\:00") & format(4316 mod
60,"\:00")

Signature

If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Reply to this Message