Conversion of seconds to hours and minutes

[Rio Liaden]

In another post requiring conversion, I could see how you helped the person, but my field is a bit different and I do not code well. My field is on a report and reads: =Sum([Seconds]). It is comprised of the sum of the seconds that I need to convert. What needs to be written to turn it into hours, minutes and seconds?

Thank you,

Rio

[PEB]

I have a proposal but it isn't perfect! You don't need to write functions it's already done!


= format(Sum([Seconds])**0.000694444444/60,"hh:mm:ss")

[PEB]

Opa!

= format(Sum([Seconds])*0.000694444444/60,"hh:mm:ss")
You have to pay attention with the decimal! If you use decimal virgul instaed point you may have problems!

[Rio Liaden]

Opa!

= format(Sum([Seconds])*0.000694444444/60,"hh:mm:ss")
You have to pay attention with the decimal! If you use decimal virgul instaed point you may have problems!

I copied and pasted the string and get an error that says:
"The expression you entered contains invalid syntax. You may have entered a comma without a preceeding value or identifier."

Rio

[PEB]

Change the point with virgula in this expression
or
better type

=format(Sum([Seconds])*val("0.000694444444")/60,"hh:mm:ss")

This have to work!

[Rio Liaden]

Change the point with virgula in this expression
or
better type

=format(Sum([Seconds])*val("0.000694444444")/60,"hh:mm:ss")

This have to work!

Danke! THAT one works! I owe you!

Rio

[Rio Liaden]

Well, I am back because, after fiddling with it, I realized that this formula

=Format(Sum([Seconds])*Val("0.000694444444")/60,"hh:nn:ss")

will not convert and get me to days, as in dd:hh:mm:ss. What, pray tell, do I need to add?

Rio

[MMcCarthy]

Your typed "hh:nn:ss" instead of "hh:mm:ss", may just be a typo.

Back to your question have you tried changing "hh:mm:ss" to "dd:hh:mm:ss".

Well, I am back because, after fiddling with it, I realized that this formula

=Format(Sum([Seconds])*Val("0.000694444444")/60,"hh:nn:ss")

will not convert and get me to days, as in dd:hh:mm:ss. What, pray tell, do I need to add?


Rio

[PEB]

Try "dd/mm/yyyy hh:mm:ss"

or "yyyy/mm/dd hh:mm:ss"

:)

[Rio Liaden]

"dd/mm/yyyy hh:mm:ss" returns 31/12/1899 31:22:05:37

and

"yyyy/mm/dd hh:mm:ss" returns 1899/12/31 22:05:37

What I need the return to be is 1:22:05:37 which represents 1 day, 22 hours, 5 mins, 37 seconds. My original total seconds is 165937.

If I use "dd:hh:mm:ss", it returns 31:22:05:37, and I know this problem exists because the clock turns over at 12:00 PM, but I really do not know how to fix it. Somebody help me!

Rio

[PEB]

So a function that can gives you the days is Datediff()

datediff("d",0,165937/60*Val("0.000694444444"))

and format gives you the other difference

You can do the following:
=datediff("d",0,Seconds/60*Val("0.000694444444"))+Format(165937/60*Val("0.000694444444"),"hh:mm:ss")

:)

[MMcCarthy]

Try using a function something like this:

 
Function TimeInterval(value As Long) As String
' pass in a value in seconds
Dim secVal As Integer
Dim minVal As Integer
Dim hourVal As Integer
Dim dayVal As Integer
 
    dayVal = value / 86400
    hourVal = (value - (86400 * dayVal)) / 3600
    minVal = (value - ((3600 * hourVal) + (86400 * dayVal))) / 60
    secVal = (value - ((60 * minVal) + (3600 * hourVal) + (86400 * dayVal)))
 
    TimeInterval = dayVal & ":" & hourVal & ":" & minVal & ":" & secVal
End Function
 
 

"dd/mm/yyyy hh:mm:ss" returns 31/12/1899 31:22:05:37

and

"yyyy/mm/dd hh:mm:ss" returns 1899/12/31 22:05:37

What I need the return to be is 1:22:05:37 which represents 1 day, 22 hours, 5 mins, 37 seconds. My original total seconds is 165937.

If I use "dd:hh:mm:ss", it returns 31:22:05:37, and I know this problem exists because the clock turns over at 12:00 PM, but I really do not know how to fix it. Somebody help me!

Rio

[Rio Liaden]

Try using a function something like this:

 
Function TimeInterval(value As Long) As String
' pass in a value in seconds
Dim secVal As Integer
Dim minVal As Integer
Dim hourVal As Integer
Dim dayVal As Integer
 
    dayVal = value / 86400
    hourVal = (value - (86400 * dayVal)) / 3600
    minVal = (value - ((3600 * hourVal) + (86400 * dayVal))) / 60
    secVal = (value - ((60 * minVal) + (3600 * hourVal) + (86400 * dayVal)))
 
    TimeInterval = dayVal & ":" & hourVal & ":" & minVal & ":" & secVal
End Function
 
 

Do I need to put a field on my report to reflect this function?

Rio

[MMcCarthy]

=TimeInterval([Seconds])

Do I need to put a field on my report to reflect this function?

Rio

[Rio Liaden]

=TimeInterval([Seconds])

Now, I'm getting a compile error. May I send you a copy of this database? It is very small, containing only one table, a query and a report.

Rio

[MMcCarthy]

Make control source of your field equal to:

[font=Arial][size=2]=TimeInterval(Sum([howlong]))[/size][/font]
[font=Arial][size=2][/size][/font]
[font=Arial][size=2]And replace the Timer Inverval function with the following one:[/size][/font]
[font=Arial][size=2][/size][/font]

 
[font=Arial][size=2]Function TimeInterval(value As Long) As String
' pass in a value in seconds
Dim secVal As Integer
Dim minVal As Integer
Dim hourVal As Integer
Dim dayVal As Integer
Dim days As Long
Dim hours As Long
Dim mins As Long[/size][/font]
[font=Arial][size=2][/size][/font] 
[font=Arial][size=2]    days = 86400
    hours = 3600
    mins = 60
 
    dayVal = Fix(value / days)
    value = value - (dayVal * days)
    hourVal = Fix(value / hours)
    value = value - (hourVal * hours)
    minVal = Fix(value / mins)
    value = value - (minVal * mins)
    secVal = value
 
    TimeInterval = dayVal & ":" & hourVal & ":" & minVal & ":" & secVal
 
End Function[/size][/font]
 

Now, I'm getting a compile error. May I send you a copy of this database? It is very small, containing only one table, a query and a report.

Rio

[MMcCarthy]

Sorry about all the formatting in previous post. It came in with a copy and paste.

Make control source of your field equal to:

TimeInterval(Sum([howlong]))

And replace the Timer Inverval function with the following one:

 
Function TimeInterval(value As Long) As String
' pass in a value in seconds
Dim secVal As Integer
Dim minVal As Integer
Dim hourVal As Integer
Dim dayVal As Integer
Dim days As Long
Dim hours As Long
Dim mins As Long
 
    days = 86400
    hours = 3600
    mins = 60
 
    dayVal = Fix(value / days)
    value = value - (dayVal * days)
    hourVal = Fix(value / hours)
    value = value - (hourVal * hours)
    minVal = Fix(value / mins)
    value = value - (minVal * mins)
    secVal = value
 
    TimeInterval = dayVal & ":" & hourVal & ":" & minVal & ":" & secVal
 
End Function
 
 

[Rio Liaden]

This is fabulous! The database is just what I needed and I hope that I will be able to show my appreciation for all of the help I was given by helping someone else. I may not be an Access whiz, but there are a number of other programs on which I am SMOKIN'!

Thanks so much once again!

Rio

[rickeyn]

I tried to use this module and it works great except the minutes and seconds do not come out right. Do you have same problem? Example I have a second value as 15037 and it comes out as 4h10m37s but it should be 4h18m9s, no?

[rickeyn]

I think it's me that calculate wrongly. Sorry for confusion.

[Jerry911]

I know this thread has been dead for a long time, I just wanted to say thanks!You helped resolve an issue on a report I was developing and was beating my head against the keyboard over.