How do you round time?

  • Thread starter Thread starter Guest
  • Start date Start date
William said:
How do you round from a time to the nearest quarter in a query?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You mean get the quarter of a date or the quarter hour of a time?

Quarter of a date:

DatePart("q",Date())

Quarter of an hour:

(CInt(Format(Time(),"nn")) \ 15) + 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4YhIechKqOuFEgEQIvOQCffoqDzjl7nVEtvAFD3rukVKhOTsoAnjzT
BHJbyMf/rcQYRFR/4mENpz63
=zQXP
-----END PGP SIGNATURE-----
 
That does not work or I just do not know how to use it. Can you explain that
code?
Or can someone tell me how to round time to the nearest quarter of an hour?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

(CInt(Format(Time(),"nn")) \ 15) + 1

Explanation:

Time() is an example time value. It is a VBA built-in function that
returns the current system Time. You would substitute the column name
that holds the time values.

The Format() function will get the minutes value ("nn" is the format
equivalent for minutes) from the time value. The minutes will be a
string data type.

The CInt() function converts the minutes from string data type to
integer data type - so we can perform mathmetical calculations on it.

The formula for finding the quarter hour of the minutes is:

(minutes \ 15) + 1

Here is the example result (from the Access Debug window):

? time
12:34:30 PM

? (CInt(Format(Time(),"nn")) \ 15) + 1
3

Since my system clock indicates minute 34, then the time is in the 3rd
quarter of the hour.

If you wanted that time in minutes you'd use this formula:

(CInt(Format(Time(),"nn")) \ 15) * 15

This rounds the value down to the closest 15th minute. E.g., if the
time was 12:34:30 PM, the formula would produce minute 30. So rounding
a complete time to the closest quarter hour:

CDate(Format(Time(),"hh") & ":" & _
Format((CInt(Format(Time(),"nn")) \ 15) * 15,"00"))

If Time = 12:34:30 PM, the above formula would yield 12:30:00 PM.

In a query substitute the name of the time column for the "Time"
function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9gm4echKqOuFEgEQLR5QCgw/XGLT32BhHIUR6gVuKVj66Xsd0AoJVu
DsMZupF0F5tr7qIBZR9l1EPk
=vcLe
-----END PGP SIGNATURE-----
 
William said:
Or can someone tell me how to round time to the nearest quarter of an hour?
Click to expand...

Round nearest: Int([YourTime]*24*4+.5)/24/4
Round down: Int([YourTime]*24*4)/24/4
Round up: -Int(-[YourTime]*24*4)/24/4

You may, of course, replace the 24*4 and/or 24/4 with 96.

-Greg.
 
Back
Top