Time question

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Im having a serious issue with the not being able to get a
sum of times in a table. So my next question is, im going
to store all the times in minutes, so when they come on a
form to view; it just needs to be broken down with MOD.
But my question is: on the form where I will be Entering
in the Times as 1:15 (meaning 1hr and 15 minutes worked
for the day), can i make the form automatically convert it
into minutes? or even 1 hr and then the 15 minutes as
fractions of an hr?
 
In reality 1:15 is 1:15 AM/PM a time, not an interval. The interval would be
1.25.

So, you can enter 2 date/time values and compute the interval, or you can
enter the interval in whatever units you wish. I'd use a single field for 75
minutes; or 2 fields for 1 hour and 15 minutes; or a single field for 1.25
hours.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Pete said:
Im having a serious issue with the not being able to get a
sum of times in a table. So my next question is, im going
to store all the times in minutes, so when they come on a
form to view; it just needs to be broken down with MOD.
But my question is: on the form where I will be Entering
in the Times as 1:15 (meaning 1hr and 15 minutes worked
for the day), can i make the form automatically convert it
into minutes? or even 1 hr and then the 15 minutes as
fractions of an hr?
Click to expand...

Sure. You could create a function like the following and call it from
the AfterUpdate event of an unbound text box:

'----- start of function code -----
Function HHMMtoMinutes(strTime As String) As Long

' Convert a string specifying minutes or hours:minutes into
' just a count of minutes.

Dim strParts() As String
Dim lngHoursToMinutes As Long
Dim lngMinutes As Long

strParts = Split(strTime, ":")

Select Case UBound(strParts)
Case 0
lngMinutes = Val(strParts(0))
Case 1
lngHoursToMinutes = Val(strParts(0)) * 60
lngMinutes = Val(strParts(1))
Case Else
Err.Raise 5, "HHMMtoMinutes", _
"Invalid argument '" & strTime & "'."
End Select

HHMMtoMinutes = lngHoursToMinutes + lngMinutes

End Function
'----- end of function code -----

You might call the function like this:

'----- start of event procedure -----
Private Sub txtTime_AfterUpdate()

If IsNull(Me.txtTime) Then
Me.Minutes = Null
Else
Me.Minutes = HHMMtoMinutes(Me.txtTime)
End If

End Sub
'----- end of event procedure -----
 
Pete, not sure if this will help. I just built a DB where I will be entering time that will be used in a calculation. To do this, I had to convert the time to a whole number. I had to creat five steps
1. creat a cell [I will call OTTime] to enter time as short time Example 1:3
2. creat a cell [I will call ABStime] with the following =ABS([OTTime] Will give you 1:3
3. creat a cell [I will call ABSHour] with the following =ABS(Hour([OTTime]) Will give you
4. creat a cell [I will call ABSMinute] with the following =ABS(Minute(OTTime]) will give you 3
5. create a cell [I will call TotalOT] with the following = Hour([ABSTime])+Minute([ABSTime])/60 Will give you 1.5

You will now have a time as a whole numbe that can be used in calculation
The only trick is if you are entering a time in #1 cell over 23:59 because the format is short time. What I did was create a second set exactly the same called OTTime2, ABStime2 etc.
You will need one more cel
6. =([TotalOT]+[TotalOT2]

Then if you have say a person that worked overtime of 25:30 hours. You would enter 23:00 in OTTime and 2:30 in OTTime2. Then in #6 you will get your whole number time as 25.50 again able to work in calculations

What I will do is "Hide" these cells behind something else on my form so no one will see the calculations being perfomed, only the final conversion in step #6./ unless you want to watch your calculations

I know this seems like a round about way, but it works for me and you know there is always more than one way to get a result

I'm hope this is not "as clear as mud
Diane
 
You can use simple code to convert to mins. For example:

?CDate("1:15") * 24 * 60
75
 
Van T. Dinh said:
You can use simple code to convert to mins. For example:

?CDate("1:15") * 24 * 60
75
Click to expand...

Yes, but only if the time given is valid as a time of day, not as a
duration of more than 24 hours. For example,

?CDate("25:15") * 24 * 60

will raise an error.
 
Back
Top