Solved

# Format elapsed time format to minutes

Posted on 2008-10-03
985 Views
I have a column on a table that stores an "elapsed time" in the following format 00:00:00 (HRS:MIN:SEC). I need to divide this time by a quantity.

Is there an easy way to do this, or will I need to break the time down into seconds then do the divide. If so, how do I do this? All these calculations will be done on a report or the query used to pull the data for the report.

Thanks!
0
Question by:filtrationproducts
• 5
• 5
• 2
• +1

LVL 8

Expert Comment

Here is a function that takes number of seconds and calculates hours, minutes and seconds. You can modify it for your purposes. The second smaller function is necessary to add an "s" to the end of the words "hour", "minute" and "second" when necessary.

``````Function SecondsToText(Seconds) As String

Dim Result As String

Dim sTemp As String

Dim years As Double 'add code

Dim months As Double 'add code

Dim days As Double

Dim hours As Double

Dim minutes As Double

If Seconds <= 0 Or Not IsNumeric(Seconds) Then

SecondsToText = "0 seconds"

Exit Function

End If

Seconds = Fix(Seconds)

If Seconds >= 86400 Then

days = Fix(Seconds / 86400)

Else

days = 0

End If

If Seconds - (days * 86400) >= 3600 Then

hours = Fix((Seconds - (days * 86400)) / 3600)

Else

hours = 0

End If

If Seconds - (hours * 3600) - (days * 86400) >= 60 Then

minutes = Fix((Seconds - (hours * 3600) - (days * 86400)) / 60)

Else

minutes = 0

End If

Seconds = Seconds - (minutes * 60) - (hours * 3600) - _

(days * 86400)

If Seconds > 0 Then Result = Seconds & " second" & AutoS(Seconds)

If minutes > 0 Then

sTemp = minutes & " minute" & AutoS(minutes)

If bAddComma Then sTemp = sTemp & ", "

Result = sTemp & Result

End If

If hours > 0 Then

sTemp = hours & " hour" & AutoS(hours)

If bAddComma Then sTemp = sTemp & ", "

Result = sTemp & Result

End If

If days > 0 Then

sTemp = days & " day" & AutoS(days)

If bAddComma Then sTemp = sTemp & ", "

Result = sTemp & Result

End If

SecondsToText = Result

End Function

Function AutoS(Number)

If Number = 1 Then AutoS = "" Else AutoS = "s"

End Function
``````
0

LVL 1

Author Comment

That is the most confusing thing I have ever seen.

Can we simplify this?

I need the calculation in VB to change this data "00:06:30" in a text field equaling (6 minutes & 30 seconds) down to only seconds and format it as a simple number (360) so I can use it in a calculation.
0

LVL 8

Assisted Solution

Emil_Gray earned 25 total points
That is what the function does but you have to convert the "00:06:30" to 390 and use the function.
It is simple to do.
Multiply the number of hours by 3600.
Multiply the number of minutes by 60.
Add the results to the number of seconds and use the final result which is the total number of seconds in the provided function.
In your example: 390 seconds used in the function returns:
6 minutes, 30 seconds
0

LVL 8

Expert Comment

Here is how simple it is:
?SecondsToText(390)
6 minutes, 30 seconds
0

LVL 23

Accepted Solution

irudyk earned 75 total points
If you just want to get the number of seconds and divide it by some other field value use (as Emil_Gray alluded to in his post):
In a report set the field's Control Source to:
=(Hour([TimeField])*3600+Minute(TimeField])*60+Second(TimeField]))/[QuantityField]
In a query (design view), add a column with the following:
CalculatedValue: (Hour([TimeField])*3600+Minute(TimeField])*60+Second(TimeField]))/[QuantityField]
Replace [TimeField] and [QuantityField] with your applicable field names.
0

LVL 1

Author Comment

irudyk,
Does it matter that the "timestudy" field that holds the 00:00:00 time is only a text field?
0

LVL 1

Author Comment

nevermind
0

LVL 8

Expert Comment

No, it doesn't matter. Here is what happens when using the following query formula with a text field of "00:06:30". If used with the 2 functions I provided you get...
SecondsToText((Hour([myTime])*3600)+(Minute([myTime])*60)+Second([myTime]))
6 minutes, 30 seconds
0

LVL 74

Expert Comment

filtrationproducts,

I think we have drifted away from your original question:
"I have a column on a table that stores an "elapsed time" in the following format 00:00:00 (HRS:MIN:SEC). I need to divide this time by a quantity.
Is there an easy way to do this?"
also:
"Does it matter that the "timestudy" field that holds the 00:00:00 time is only a text field?"

I used this, and it worked beautifully,
(Yes, Timestudy is Text in this example)

SELECT tblElapsedTime.TimeStudy, tblElapsedTime.Divisor, Format(CDate([TimeStudy])/[Divisor],"hh:nn:ss") AS Result
FROM tblElapsedTime;

Basically, this converts the Text, (Timestudy), to Date/Time, divides it by the divisor and then formats the result as Date/Time
For example:
1:30:30 divided by 2, results in: 00:45:15
05:22:16 divided by 2, results in: 02:41:08
9:45:09 divided by *3*, results in: 03:15:03

Here is a  sample

If this is what you want, great.
If not, then please give an example of your Elapsed Time, a divisor, and the *exact* result you want.

Thanks

JeffCoachman

Access-EEQ23785086-DivideElapsed.mdb
0

LVL 74

Expert Comment

filtrationproducts

You need minutes only.

I am sure the other experts here can get you going.
;-)

JeffCoachman
0

LVL 8

Expert Comment

I do not understand why this seems so difficult. I have already provided a function that does what you want.
All you need do is use the code I provided earlier to create two functions. Then in a query use the following substituting "myTime" with the name of the field holding the text data.
SecondsToText((Hour([myTime])*3600)+(Minute([myTime])*60)+Second([myTime]))
It couldn't be simpler.
0

LVL 1

Author Comment

sorry for the delay, I dont work on the weekends. But irudyk's code was very simple and did exactally what I wanted.

"In a report set the field's Control Source to:
=(Hour([TimeField])*3600+Minute([TimeField])*60+Second([TimeField]))/[QuantityField]"

That is the line of code I was looking for. Kudos irudyk.

Also everyone else, thanks for your help!
0

LVL 1

Author Closing Comment

Solution:
=(Hour([TimeField])*3600+Minute(TimeField])*60+Second(TimeField]))/[QuantityField]
0

## Featured Post

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…