Format elapsed time format to minutes

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 bAddComma As Boolean
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
    bAddComma = Result <> ""
    
    sTemp = minutes & " minute" & AutoS(minutes)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
 
If hours > 0 Then
    bAddComma = Result <> ""
    
    sTemp = hours & " hour" & AutoS(hours)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
 
If days > 0 Then
    bAddComma = Result <> ""
    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

Select allOpen in new window

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.

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

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

nevermind

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

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

filtrationproducts

Hmmm, I just re-read your post.

You need minutes only.

Sory about that.

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

JeffCoachman

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.

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!

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