Rounding & Formating Time

[pwinters]

I am calculating how long a job runs using jsp & sql to query a MSAccess DB . I have a StartTime (6:14:33) and EndTime (20:07:23) and I have calculated the time TotalTime (=15:52:50).

Code:
Format((StartTime - EndTime), 'HH:MM:SS') AS TotalTime

My question is: Is there a way to
1.) Round to the nearest quarter hour? and
2.) Display the results as "16 hr 0 min"?

Thanks for your help

 

[pwinters]

Not that it matters much...but there was a typo in my explanation above...EndTime is 22:07:23
Thanks

 

[Norris68]

Probably easiest using a public function (paste following into code module).

Public Function RoundTime(NoMinutes As Long) As String
Dim A As Long
A = NoMinutes Mod 15 'Divide no mins by 15 & find remainder
If A >= 8 Then 'If 8 mins or above, round up
A = NoMinutes + 15 - A
Else 'Round down
A = NoMinutes - A
End If
RoundTime = CStr(A \ 60) & " hrs " & CStr(A Mod 60) & " min"
End Function

Usage in your query:
SELECT RoundTime(DateDiff("n",StartTime,EndTime)) AS TotalTime...

(DateDiff is the 'correct' way to find out the difference between dates/times. "n" parameter specifies difference in minutes)

 

[pwinters]

Thanks for your response! I'm going to try it out now. Could you tell me what the backslash is for in CStr(A\60)?

 

[Norris68]

A backslash division is an integer division i.e. nothing after the decimal point. The forward slash division is a conventional division that will return a fractional part.

i.e.
(7\3) = 2
(7/3) = 2.33*
(7 Mod 3) = 1 (the remainder from an integer division)

Because we have already rounded the number of minutes to the nearest 15, we just want to know how many complete lots of 60 there are (hours) and what the remainder is (minutes).

 

[MichaelRed]

hmmmmmmmmmm ... it does "work" if applied properly. On the other hand, I didn't notice any 'helpful hints' toward that "popper' application, and think the user who needs to post the question MAY not instantly recognize the subtle necessities of that application.

To wit, the question shows the calculation in the standard "short time" format, whilst the procedure requires a long data type. The short time format is in fact the 'decimal' portion of a double, so sending the 'results' of the original (or correctd) post will simply result in 0 Hr and 0 Min.

for example:


? RoundTime(#15:52:50#)
0 hrs 0 min


So, some adjustment appears to be necessary to actually have the procedure utilize the 'value' from the original post and return the desired value. A number of different ways to do so, one (rather shakey approach) might be:

? RoundTime(dateDiff("n", StartTime, EndTime))
16 hrs 0 min


However this will (for those not familiar with the generic behaviour of Ms. Date/Time calculations may give an occassional incorrect answer, as the return value counts the number of transitions of the time unit, and does not 'round' the answer, so a slight difference in expected and actual returns MAY occur:

MyStrtTm = #6:14:33#
MyEndTm = #22:06:31#
? Format((DateDiff("s", MyStrtTm, MyEndTm)/86400), "hh:mm:ss&quot
15:51:58
? RoundTime(dateDiff("n", MyStrtTm, MyEndTm))
15 hrs 45 min



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[pwinters]

While your answers may work, part of the problem is that I am not using MSAccess other than to store the data. I am querying the data via the web using msSql in .jsp pages. So, I am taking these suggestions and trying to manipulate them so they will work with my code.

Unfortunately, I don't have much experience with VB.

I've almost got Norris68's suggestion working.
Thanks for your help.

 

[Norris68]

Michaelred, the rounding occurs during the section:

A = NoMinutes Mod 15 'Divide no mins by 15 & find remainder
If A >= 8 Then 'If 8 mins or above, round up
A = NoMinutes + 15 - A
Else 'Round down
A = NoMinutes - A
End If

Clues to the usage are: I gave an example; I called the parameter 'NoMinutes'; the input parameter is Long data type, not Date; and I pointed out that "n" causes DateDiff to return the difference in minutes.

The accuracy of the function is dependant on the accuracy of the DateDiff function; however as we are rounding up to the nearest 15 minutes, a decrepency of one minute is not going to make too much difference to the result. 15:51:58 rounded to the nearest 15 minutes is 15 hrs 45 min (6 mins 58 secs past quarter-to, 8 mins 2 secs before the hour -> quarter-to is the closest, hence the one we round to)