Calculate hours worked minus lunch

[becky250]

Im sure this has been asked before but i cant seem to find the right code. I
have fields that are Start Time, Finish Time, Lunch In, Lunch Out and Hours
Worked. I would like to create something that calculates the time worked in
between start time and finish time minus the time between lunch in and lunch
out. This would be known as Hours Worked. So if it was 9-5 with an hour for
lunch total hours worked would be 7. All values are in as time except for the
hours worked which would just be a number. Ive seen codes before that
calculate the time in between but im not sure how to calculate the time taken
for lunch also and take this away from the total.

Any help appreciated.

 

[tina]

assuming that LunchOut is when the employee clocks out for lunch, and
LunchIn is when the employee clocks back in after lunch, and assuming that
all four fields are date/time fields (not Number fields), try

HoursWorked: DateDiff("h",[[StartTime],[LunchOut]) +
DateDiff("h",[LunchIn],[FinishTime)

that will return whole hours. or you can change the interval argument from
"h" hours to "n" minutes, and then divide the total by 60 to get hours.

hth

 

[Klatuu]

=(DateDiff("n", [Start Time], [End Time]) - DateDiff("n", [Lunch In], [Lunch
Out])) /60

 

[Dale_Fye via AccessMonster.com]

First off, do you really need to store that calculated value? This is not
generally a good idea, unless you are using something like SQL Server which
will allow you to run a trigger if a field gets updated. Normally, you are
better off calculating these values when you need them, via a query.

How do you want to handle the situation if any of the values are missing (if
they don't take lunch, or forget to enter a value)?

The solutions provided by the others will work great if all the values are
entered, but don't take into account the reality of humans entering data.
I'd probably write a function which I passed all 4 fields to as Variant data
types (to allow for NULLs). Then I'd implement some business logic to handle
the situations mentioned above.

HTH
Dale

 

[Klatuu]

There you go again, Dale. Imposing reality on an otherwise perfect solution.
But, I did forget to get my soapbox out on the store calculation issue.