[AccessD] Formatting time - minutes to hour:minutes

Cindy,

I wrote the following to calculate Minutes and Seconds...with a few 
changes...you could use it to calculate Hours and Minutes.  I first 
calculate the SECONDS...then use a loop that increments a count by 
1...multiply the counter by 60 and check againts SECONDS...when the 
Counter*60 is greater than SECONDS...Counter-1 = MINUTES and 
SECONDS-((counter-1)*60)=LeftOverSECONDS

Hope it helps...

Mark A. Matte

Private Sub cmdStop_Click()
Me!EndDTTM = Now()
Dim Seconds
Dim RMmin
Dim RMsec
Seconds = DateDiff("s", Me!StartDTTM, Me!EndDTTM)
Dim Check, Counter
Check = True: Counter = 0

Do
    Counter = Counter + 1
    If Counter * 60 > Seconds Then
        RMmin = Counter - 1
        RMsec = Seconds - ((Counter - 1) * 60)
        Check = False
        Exit Do
    End If
Loop Until Check = False
Forms!frmRecords!RMCallLengthM.Value = RMmin
Forms!frmRecords!RMCallLengthS.Value = RMsec
DoCmd.close
End Sub


>From: Gustav Brock <gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Formatting time - minutes to hour:minutes
>Date: Thu, 8 Jan 2004 19:15:52 +0100
>
>Hi Lambert
>
> > If there's more that 24 * 60 minutes then TimeSerial will spit out a
> > date/time like this...
>
> > ? TimeSerial(26,1,1)
> > 12/31/1899 2:01:01 AM
>
>Of course, that's why I included the TimeHour() function.
>If you wish to count more than 24 hours, here's a quick solution for
>seconds. One line (no line break):
>
>  = Format(622081 \ 3600, "00:") &
>    Format((622081 Mod 3600) \ 60, "00:")  &
>    Format(622081 Mod 60, "00")
>
>The backslash rounds down, thus this will return a string:
>     172:48:01
>
> > The problem that Cynthia is having is simply that she is interested in 
>Time
> > DURATION, but all the Time formatting and TimeSerial, DateDiff stuff is
> > concerned with POINTS in time (a date and a time of day).
>
> > "Ugly" or not, when dealing with durations (which have nothing to do 
>with
> > dates) you always have to do the math yourself to get hour minutes and
> > seconds displayed. All you need is a simple function that given a number 
>of
> > minutes (or seconds) will do the math and formatting and return the
> > appropriate STRING value for display of reports and forms.
>
>For the "time duration" that is right. However, if you create a pseudo
>timevalue (a time from Access' time "zero"), you have the Format()
>function and all other formatting capabilities of Access ready at hand
>- thus you eliminate the need to create even a simple function.
>
>For "always do the math yourself" I see no reason to struggle with
>that; DateDiff() works fine except for a minor bug in the week count
>for some leap years.
>
>/gustav
>
>
> >> If you wish to use the features of formatting a date/time-value you
> >> need to convert the amount of minutes to a timevalue - like here where
> >> the amount of time is in hours:
> >>
> >> <code>
> >>
> >> Function TimeHour(ByVal curHMS As Currency) As Date
> >>
> >> ' Rounds and converts curHMS (number of hours as decimalnumber).
> >> ' Returns a time value, hh:nn:ss.
> >> ' Gustav Brock, Cactus Data ApS.
> >> ' 1999-08-12.
> >>
> >>   Dim lngH As Long
> >>   Dim lngM As Long
> >>   Dim lngS As Long
> >>   Dim curR As Currency
> >>
> >>   ' No error handling needed.
> >>   On Error Resume Next
> >>
> >>   ' Round to two decimals, and skip number of days if curHMS > 24.
> >>   ' If day count is needed, omit modulus like this:
> >>   ' curHMS = (CLng(curHMS * 10000) / 100 * 36) ' Mod 86400
> >>   curHMS = (CLng(curHMS * 10000) / 100 * 36) Mod 86400
> >>
> >>   ' Calculate hours, minutes, and seconds.
> >>   lngH = Int(curHMS / 3600)
> >>   curR = curHMS - (lngH * 3600)
> >>   lngM = Int(curR / 60)
> >>   curR = curR - (lngM * 60)
> >>   lngS = CInt(curR)
> >>
> >>   ' Create time value.
> >>   TimeHour = TimeSerial(lngH, lngM, lngS)
> >>
> >> End Function
> >>
> >> </code>
> >>
> >> Or you could try to simply feed your amount of minutes directly:
> >>
> >>   datTime = TimeSerial(0, lngMinutes, 0)
> >>
> >> That may work - haven't tested it though.
> >>
> >> /gustav
> >>
> >>
> >> > I have an application that tracks the amount of time spent on 
>projects.
> >> I used DateDiff on the entry form in order to get the number of minutes
> >> between the start and end times.  The number is held
> >> > in a field called "Time."  In a report, I sum "Time" in order to get 
>the
> >> total number of minutes.    I need to show the total minutes in hh:mm
> >> format on the report.  I tried to simply format the
> >> > field in the report, but that didn't work.
> >>
> >> > I tried dividing the minutes by 60, which gave me the number of hours
> >> and a remainder.  I then multiplied the remainder by 60 and then 
>divided
> >> by 100 to get the number of seconds.  This is getting
> >> > ugly....  Am I making this harder than it is??
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Check your PC for viruses with the FREE McAfee online computer scan.  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963