With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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!

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!

```
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
```

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.

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

irudyk,

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

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

SecondsToText((Hour([myTim

6 minutes, 30 seconds

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])/

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

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

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([myTim

It couldn't be simpler.

"In a report set the field's Control Source to:

=(Hour([TimeField])*3600+M

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

Also everyone else, thanks for your help!

All Courses

From novice to tech pro — start learning today.

In a report set the field's Control Source to:

=(Hour([TimeField])*3600+M

In a query (design view), add a column with the following:

CalculatedValue: (Hour([TimeField])*3600+Mi

Replace [TimeField] and [QuantityField] with your applicable field names.