Gustav Brock
gustav at cactus.dk
Fri Jan 9 04:47:07 CST 2004
Hi Lambert > But your TimeHour function returns a Date data type, and it uses TimeSerial > to generate the return value, so once again, if your input value is more the > 24 * 60 minutes then you will get back a nonsense date and time value. How do you figure this out? It will not - unless you specifically wish to by modifying a code line as explained in the in-line comment. Have in mind, please, that the TimeHour() function is not meant to represent some kind of "universal" function. It was made for summing time intervals which would never add to one day. There a many cases for this in real life, starting/stopping engines, billable hours etc. where you always group by date. Cynthia's need could very well be similar. > I repeat, if you are interesting in time durations then you need to work > with numerical values, not Date/Time values. The Access Date/Time data type > is inherently tied to the concept of a date - a point on the time line, > which has nothing to do with the idea of a duration. This is why DateDiff > returns a Long value, not some kind of date value. I do understand you and if you look up the archives you will find several messages from me stating the same; in fact, I prefer this method if for nothing else that you get rounded values by, say, the second and prevents the mess you can experience when doing algebra with Doubles and Singles. However, if you shift a time interval calculated from two absolute time values (start and stop) so that the start time value is at zero (which is what DateSerial() and TimeSerial() do) then the time interval can be expressed by the time value of the stop time. This doesn't trun the time interval into a "time" but it allows you to use the date/time datatype which again will allow you to use the standard date/time formatting capabilities of Access; this may or may not be advantageous - that depends on many factors including your own preferences. /gustav > "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") > " > Exactly, this is just the type of code you would put in a custom function to > format durations so they display as hr:min:sec. Why type all that code each > time you want to format a duration (like 62,2081 seconds) when you can just > call a function - FormatAsTime(622081) ???? > Lambert >> -----Original Message----- >> From: Gustav Brock [SMTP:gustav at cactus.dk] >> Sent: Thursday, January 08, 2004 1:16 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [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??