Rounding up to the nearest Quarter Hour

  • Thread starter Thread starter Demonicpagan
  • Start date Start date
D

Demonicpagan

I have a MS Acess DB that takes in an employee's clock in time
[start_time], their clock out time [end_time] (formatted as Date/Time -
Medium Time). What I want to be able to do with these is calculate the
actual hours worked, which I have seemed to have figured out:

SELECT Time.emp_id, Time.pos_id, Time.start_time, Time.end_time,
(DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))\60
&
Format((DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))
Mod 60,"\:00") AS ActHours FROM Time

and the rounded hours (similar to what you would get a time clock,
rounded up to the nearest quarter of an hour).

What I have been using to calculate this hasn't been working.

RndHours:
(DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))\60
&
Format(15*Round((DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))/15,0)
Mod 60,"\.00")

This produces a result of 8.15 and it should come back with 8.25.
Where is my error so I can fix it? I've browsed about these newsgroups
countless number of times and haven't come across anything that would
remotely help me. FYI: These are being done in a query.

Help would be greatly appreciated. Thanks for anyone's time and effort
that they are able to provide.
 
An expression like this will round up to the nearest 15:

(x \ 15 + Abs(Sgn(x / 15 - x \ 15))) * 15

This one is a bit slower but easier to read:

IIF(x / 15 <> x \ 15, (x \ 15 + 1) * 15, x)

I have a MS Acess DB that takes in an employee's clock in time
[start_time], their clock out time [end_time] (formatted as Date/Time -
Medium Time). What I want to be able to do with these is calculate the
actual hours worked, which I have seemed to have figured out:

SELECT Time.emp_id, Time.pos_id, Time.start_time, Time.end_time,
(DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))\60
&
Format((DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))
Mod 60,"\:00") AS ActHours FROM Time

and the rounded hours (similar to what you would get a time clock,
rounded up to the nearest quarter of an hour).

What I have been using to calculate this hasn't been working.

RndHours:
(DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))\60
&
Format(15*Round((DateDiff("n",[start_time],[end_time])+IIf([start_time]>[end_time],24*60,0))/15,0)
Mod 60,"\.00")

This produces a result of 8.15 and it should come back with 8.25.
Where is my error so I can fix it? I've browsed about these newsgroups
countless number of times and haven't come across anything that would
remotely help me. FYI: These are being done in a query.

Help would be greatly appreciated. Thanks for anyone's time and effort
that they are able to provide.
Click to expand...
 
How would I go about using that in my current formula. What is it that
I need to replace to get this to work? I'm thrown off by the variable
x in your equation.
 
Basically you insert the expression into your current formula anywhere
you want (minutes rounded up to the nearest quarter hour), replacing "x"
with whatever expression gives you the number of minutes you want to
round up.
 
Back
Top