round function?

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

Hi,

I need to round time to the nearest 0, 15, 30 or 45 Minutes. Example:

START SHIFT (ROUND UP)
8:10 AM to 8:15 AM
8:15 AM to 8:15 AM
8:16 AM to 8:30 AM
8:56 AM to 9:00 AM

END SHIFT (ROUND DOWN)
8:10 AM to 8:00 AM
8:15 AM to 8:15 AM
8:16 AM to 8:15 AM


I cannt find a solution for this problem. Any help would be very much
appreciated.

Thank

Bre-x
 
Hi,

I need to round time to the nearest 0, 15, 30 or 45 Minutes. Example:

START SHIFT (ROUND UP)
8:10 AM to 8:15 AM
8:15 AM to 8:15 AM
8:16 AM to 8:30 AM
8:56 AM to 9:00 AM

END SHIFT (ROUND DOWN)
8:10 AM to 8:00 AM
8:15 AM to 8:15 AM
8:16 AM to 8:15 AM


I cannt find a solution for this problem. Any help would be very much
appreciated.
Click to expand...

You can take advantage of the fact that an Access Date/Time value is actually
a number, a count of days and fractions of a day. Since there are 96 15-minute
periods in a day you can multiply the date/time value by 96, get the integer
portion, and divide back.

Your first line says "nearest" but from your examples it's obviously not the
nearest, but rather up/down. For End Shift try

CDate(Int(CDbl([datefield]) * 96)/96)

and for Start Shift

CDate(-Int(CDbl(-[datefield])*96)/96)

The Int function truncates down - so positive numbers will round toward zero,
negative numbers away from zero. The double negative will end up rounding up.

If you want to use the current system clock time use Now() in place of
[datefield], to include the current date and time; or Time() if you just want
the time (though I'd really recommend storing the date and time together).
 
Thanks!!!

Bre-x


John W. Vinson said:
Hi,

I need to round time to the nearest 0, 15, 30 or 45 Minutes. Example:

START SHIFT (ROUND UP)
8:10 AM to 8:15 AM
8:15 AM to 8:15 AM
8:16 AM to 8:30 AM
8:56 AM to 9:00 AM

END SHIFT (ROUND DOWN)
8:10 AM to 8:00 AM
8:15 AM to 8:15 AM
8:16 AM to 8:15 AM


I cannt find a solution for this problem. Any help would be very much
appreciated.
Click to expand...

You can take advantage of the fact that an Access Date/Time value is
actually
a number, a count of days and fractions of a day. Since there are 96
15-minute
periods in a day you can multiply the date/time value by 96, get the
integer
portion, and divide back.

Your first line says "nearest" but from your examples it's obviously not
the
nearest, but rather up/down. For End Shift try

CDate(Int(CDbl([datefield]) * 96)/96)

and for Start Shift

CDate(-Int(CDbl(-[datefield])*96)/96)

The Int function truncates down - so positive numbers will round toward
zero,
negative numbers away from zero. The double negative will end up rounding
up.

If you want to use the current system clock time use Now() in place of
[datefield], to include the current date and time; or Time() if you just
want
the time (though I'd really recommend storing the date and time together).
Click to expand...
 
Back
Top