[AccessD] Formatting time - minutes to hour:minutes

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