Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format elapsed time format to minutes

Posted on 2008-10-03
13
Medium Priority
?
996 Views
Last Modified: 2013-11-29
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
Comment
Question by:filtrationproducts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 22635233
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

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 22635379
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

by:Emil_Gray
Emil_Gray earned 100 total points
ID: 22635526
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 8

Expert Comment

by:Emil_Gray
ID: 22635621
Here is how simple it is:
?SecondsToText(390)
6 minutes, 30 seconds
0
 
LVL 23

Accepted Solution

by:
irudyk earned 300 total points
ID: 22637188
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

by:filtrationproducts
ID: 22637676
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

by:filtrationproducts
ID: 22637754
nevermind
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 22637947
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

by:Jeffrey Coachman
ID: 22643164
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

by:Jeffrey Coachman
ID: 22644670
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
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 22645136
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

by:filtrationproducts
ID: 22651561
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

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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question