time formula

[lparsons]

Hi:
I am working on a query to calculate hours worked. I have a 'timein'
field
and a 'timeout' field. I want the query to calculate the difference
and subtract 30 minutes for the lunch. I also want the time rounded
by quarter hour, for example
if arrival time was 7:05, it would round down to 7:00. If time out was
5:10
it would use 5:15. Can I do this calculation in access?
Thanks in advance for your help.

Lois

 

[John Vinson]

Hi:
I am working on a query to calculate hours worked. I have a 'timein'
field
and a 'timeout' field. I want the query to calculate the difference
and subtract 30 minutes for the lunch. I also want the time rounded
by quarter hour, for example
if arrival time was 7:05, it would round down to 7:00. If time out was
5:10
it would use 5:15. Can I do this calculation in access?
Thanks in advance for your help.

Let's take this in steps. The DateDiff function can get the elapsed
time in any time units you want: e.g.

DateDiff("n", [timein], [timeout])

would give the miNutes ("m" is Months).

Of course you can subtract 30 from this (which may be in error if an
employee clocks out before lunch, but...)

To round to the nearest quarter hour, you can do an integer divide by
15 and multiply that integer by 15:

((DateDiff("n", [timein], [timeout]) - 30) \ 15) * 15)

This will give 450 minutes for timein = 08:00 and timeout = 04:00. You
can use some more complex expressions to format this as 07:30 if you
wish.

 

[Allen Browne]

Louis, paste the code below into a Module.
You can then call it in a query by typing something like this into the field
row:
HoursWorked([YourTimeInFieldHere], [YourTimeOutFieldHere])


Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CLng(DateDiff("n", #12:00:00 AM#,
TimeValue(TimeIn)) / 15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CLng(DateDiff("n", #12:00:00 AM#,
TimeValue(TimeOut)) / 15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) -
lngcLunchBreak) / 60
End If
End If
End Function

 

[Van T. Dinh]

I think you meant:

CInt((DateDiff("n", [timein], [timeout]) - 30) / 15) * 15

for rounding to the nearest 15 mins.

Cheers
Van

Hi:
I am working on a query to calculate hours worked. I have a 'timein'
field
and a 'timeout' field. I want the query to calculate the difference
and subtract 30 minutes for the lunch. I also want the time rounded
by quarter hour, for example
if arrival time was 7:05, it would round down to 7:00. If time out was
5:10
it would use 5:15. Can I do this calculation in access?
Thanks in advance for your help.

Let's take this in steps. The DateDiff function can get the elapsed
time in any time units you want: e.g.

DateDiff("n", [timein], [timeout])

would give the miNutes ("m" is Months).

Of course you can subtract 30 from this (which may be in error if an
employee clocks out before lunch, but...)

To round to the nearest quarter hour, you can do an integer divide by
15 and multiply that integer by 15:

((DateDiff("n", [timein], [timeout]) - 30) \ 15) * 15)

This will give 450 minutes for timein = 08:00 and timeout = 04:00. You
can use some more complex expressions to format this as 07:30 if you
wish.

 

[Douglas J. Steele]

\ returns the integer result of the division, so

DateDiff("n", [timein], [timeout]) - 30) \ 15

and

CInt((DateDiff("n", [timein], [timeout]) - 30) / 15)

are identical


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I think you meant:

CInt((DateDiff("n", [timein], [timeout]) - 30) / 15) * 15

for rounding to the nearest 15 mins.

Cheers
Van

Hi:
I am working on a query to calculate hours worked. I have a 'timein'
field
and a 'timeout' field. I want the query to calculate the difference
and subtract 30 minutes for the lunch. I also want the time rounded
by quarter hour, for example
if arrival time was 7:05, it would round down to 7:00. If time out was
5:10
it would use 5:15. Can I do this calculation in access?
Thanks in advance for your help.

Let's take this in steps. The DateDiff function can get the elapsed
time in any time units you want: e.g.

DateDiff("n", [timein], [timeout])

would give the miNutes ("m" is Months).

Of course you can subtract 30 from this (which may be in error if an
employee clocks out before lunch, but...)

To round to the nearest quarter hour, you can do an integer divide by
15 and multiply that integer by 15:

((DateDiff("n", [timein], [timeout]) - 30) \ 15) * 15)

This will give 450 minutes for timein = 08:00 and timeout = 04:00. You
can use some more complex expressions to format this as 07:30 if you
wish.

 

[Van T. Dinh]

Hi Doug

The difference is that John's expression rounds down to the lower 15 mins
while mine rounds to the nearest 15 mins.

 

[Phobos]

John doesn't want to overpay his (or anybody elses) employees! <g>

P

Hi Doug

The difference is that John's expression rounds down to the lower 15 mins
while mine rounds to the nearest 15 mins.

--
Cheers
Van

\ returns the integer result of the division, so

DateDiff("n", [timein], [timeout]) - 30) \ 15

and

CInt((DateDiff("n", [timein], [timeout]) - 30) / 15)

are identical

 

[Douglas J. Steele]

Ah, sorry. I missed the CInt (which rounds), as opposed to Int (which
truncates)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Doug

The difference is that John's expression rounds down to the lower 15 mins
while mine rounds to the nearest 15 mins.

--
Cheers
Van

\ returns the integer result of the division, so

DateDiff("n", [timein], [timeout]) - 30) \ 15

and

CInt((DateDiff("n", [timein], [timeout]) - 30) / 15)

are identical

 

[lparsons]

THANKS for your help. I think this makes the total time in minutes,
and I need hours. Do I need to do another field to divide by 6o to get
hours or can I
modify this formula?

Thanks again for the help.

Lois

 

[Ken Snell]

Allen's function returns the time in fractional hours.