[AccessD] Formatting time - minutes to hour:minutes

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 

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.

Lambert

> -----Original Message-----
> From:	Gustav Brock [SMTP:gustav at cactus.dk]
> Sent:	Thursday, January 08, 2004 11:42 AM
> To:	Access Developers discussion and problem solving
> Subject:	Re: [AccessD] Formatting time - minutes to hour:minutes
> 
> Hi Cynthia
> 
> 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