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.0006944444 44/60,"hh:mm:ss")

[PEB]

Opa!

= format(Sum([Seconds])*0.00069444444 4/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.00069444444 4/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.000694 444444")/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.000694 444444")/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.000694 444444")/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:s s".

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

=Format(Sum([Seconds])*Val("0.000694 444444")/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:s s", 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.00069 4444444"))

and format gives you the other difference

You can do the following:
=datediff("d",0 ,Seconds/60*Val("0.00069 4444444"))+Form at(165937/60*Val("0.00069 4444444"),"hh:m m: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:s s", 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