Tags: access, across, andhavent, archives, database, half, increments, instances, ive, microsoft, mysql, oracle, pages, personal, quarter-hour, rounding, scanning, sql, time

Rounding Time to Half or Quarter-Hour Increments

On Database » Microsoft Access

3,519 words with 1 Comments; publish: Thu, 05 Jun 2008 09:47:00 GMT; (25078.13, « »)

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

All Comments

Leave a comment...

  • 1 Comments
    • > 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)

      "Sharkbyte" <sharkbyte1.ms-access.itags.org.hotmail.com-> wrote in message

      news:7C8BC6F3-DD30-44B7-943A-F037FC59AF2F.ms-access.itags.org.microsoft.com...

      > 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

      #1; Thu, 05 Jun 2008 09:48:00 GMT