Rounding Time to Half or Quarter-Hour Increments

[Sharkbyte]

I've been scanning the archives, and some of the personal Access pages, and
haven't happened across this, yet...

I have 2 instances, in a db, where I need to round time. One to the
half-hour, one to the quarter-hour. In both cases, I am calculating a time
difference and producing a decimal. I easily can go to Tenths, but the
customer wants the other.

To add another level of complexity, I'm fairly certain the customer would
want any partial time to roll up. ie. 3 hrs. 2 min. rounds to 3.5 hours.

Here is the code I was using to round to Tenths. I just can't figure out
what I need to do to get to halves or quarters.

Round(FormatNumber(((Time() - gblArrivalTime) * 24), 2), 1)

Thanks, in advance.

Sharkbyte

 

[david epsom dot com dot au]

Round(FormatNumber(((Time() - gblArrivalTime) * 24), 2), 1)

quarter hours:
fix(0.2499999 + (time() - gblArrivalTime) * 24 * 4) /4


times 4 to get quarter hours instead of full hours.

divide by 4 to get full hours back after rounding.

add slightly less than 0.25 hours to get extra quarter hour rounding up.

use Fix to discard all the fractional quarter hours after converting to
quarter hours and adding 0.25, before converting back to full hours.

half hours:
fix(0.499999 + (time() - gblArrivalTime) * 24 * 2) /2

full hours exactly:
fix(0.999999 + (time() - gblArrivalTime) * 24)

tenth of an hour:
fix(0.099999 + (time() - gblArrivalTime) * 24 * 10)/10)

Note that in your original expression you used two different rounding
functions, when either one could be used.

Note also that solutions using Round or FormatNumber will give funny
answers, because (1), They always round to a decimal fraction of a day, but
since a day has 12 hours, the exact rounding point will not be the median
point of the hour. And (2), because they use Bankers Rounding, which is nice
for bankers, but probably not what your client wants here.

That is, the rounding functions will round to .1 hours, but you can't
predict which minutes will round up, and which will round down. The answer
will be unexpected for some minutes.

To avoid this, you need to use a custom Rounding function.

After rounding, you may also wish to reformat the number for display (so
that you see 0.50, instead of 0.5 or 0.4999999 or 0.5000001) The built-in
rounding and formatting functions are safe for this.

(david)