Readers offer their solutions for calculating total hours.
Many readers have contributed better (or at least different) functions or expressions as alternatives to the rather awkward method I described in AW 7.04. Here they are for your consideration, with my comments. (Bill Patterson’s contribution was described in AW 7.10).
For testing and comparison, I am using the test times of 7:00 AM start and 5:23 PM end on the same day for those functions taking the start and end dates as arguments. For those times, rounded up to the next highest quarter-hour (which is what was wanted in the Fancy Filters database), the value of TotalHrs should be 10.5.
Bill Grady
First get the # of quarter hours rounded up:
q = -int(DateDiff(“n”,[EndTime],[StartTime])/15)
Then convert that to hours
TotalHrs = int(q/4) + (q mod 4)*.25
Comments: Yields 10.5 (correct).
Tim Boreham
TotalHrs: Int((DateDiff(“n”, dteStartTime, dteEndTime) + 14) / 15) / 4
Comments: Yields 10.5 (correct).
Andrew Crisp
sngDiffHrs = ((DateDiff(“n”, dtmBegin, dtmEnd) 15) + 1) / 4#
The backslash forces and integer division which gives the number of quarter hours (which is adjusted by the +1 to round up – e.g. 37 minutes up to 45 minutes). The further division by a single value of 4 (4#) converts to hours. The one difficulty is that 0 minutes will round up to 15 minutes – which clients may find objectionable . . .
Comments: Yields 10.5 for the sample times (correct). However, the client undoubtedly would object to 0 minutes rounding up to 10 – testing with 7:00 am start and 5:00 pm end did indeed yield 10.25, where it should have been 10.
John Allcock
Public Function RoundToQuarters(incoming As Single) As Single
RoundToQuarters = Int((incoming + 0.24) * 4) / 4
End Function
Comments: Assuming that the incoming argument represents the number of hours (converted to decimal), I tested with various values, and it rounded up correctly in some cases (1.11 or 1.24 yielded 1.25), but not in others (1.01 yielded 1).
Trevor Mills
Dim dblStart As Double
Dim dblStartRoundedUp As Double
dblStart = 1.001
dblStartRoundedUp = Round((dblStart + 0.125) * 4, 0) / 4
Please note that this is not my solution. I found the rounding suggestion on www.experts-exchange.comand simply modified it to add the 1/8 hour to force the rounding to the next quarter of an hour.
Comments: Assuming that dblStart represents the number of hours (converted to decimal), I tested with various values, and it rounded up correctly in some cases (1.01 or 1.24 yielded 1.25), but not in others (1.26 yielded 1.25).
Graeme Williams
My suggestion is to convert minutes to units of quarter hours, not hours. Since you want to round up (and not down), you really need the “ceiling” function, missing from VBA:
TotalHrs: Ceiling(DateDiff(“n”,[StartTime],[EndTime])/15)/4
but you can fake it with the arithmetic equivalent of a double-negative:
TotalHrs: -Int(-DateDiff(“n”,[StartTime],[EndTime])/15)/4
The documentation on Int() and Fix() sheds a little light on this.
Comments: Yields 10.5 (correct).
Gustav Brock
In general, numbers should be handled like numbers.
We use the function below for a similar purpose.
It can be further simplified to a one-liner:
HoursRounded = -Int(-RawTotalHrs / 0.25) * 0.25
Comments: Yields 10.25 for RawTotalHrs values from 10.1 to 10.24 (correct).
