Calculating Overtime hours from Time entries

[weshader]

Hello All,



I am still fairly new to vba, so The problem I am having seems VERY hard to overcome. Hopefully, one of the "Pro's" will be able to solve this for me.

I have built a Time Clock database that records; "Time In" and "Time Out" for each employee, and stores those records in a table called tblTIMES.

I have figured out how to calculate the total hours worked for a specific date range, but have not figured out how to calculate the amount of hours worked that are eligible for overtime. Below I will try to lay out my problem in detail.

The tblTIMES table has the following fields: [TimeID] as AutoNumber, [EmployeeNo] as Number, [ClockInDate] as Date/Time, [TimeIn] as Date/Time, [ClockOutDate] as Date/Time, [TimeOut] as Date/Time, [TimeType] as Text, [TimeComments] as Text, and [CurStatus] as Text.

I use a function called HoursAndMinutes() to calculate the time difference between the [TimeOut] and [TimeIn] fields and format it into a decimal number (e.g. 7.5 = 7 hours and 30 minutes).

Each Clock In/Out Pair is stored as a SINGLE record in the tblTIMES Table. I run reports in which the start date and end date will vary, depending on the time period selected. The report lists each record for the selected period([ClockInDate],[TimeIn], [ClockOutDate], [TimeOut], [TimeType], and [Total Hours] Which is calculated using the aforementioned function "HoursAndMinutes()").

Now here is my problem. I need to determine which records are in a specified week, calculate the time worked for that week, determine if overtime pay is needed, and calculate the total hours of overtime.

Overtime is paid when an employee works more than 40 hours per week in which there are no vacation days or holidays. Each record in the tblTIMES table has a [TimeType] Field that specifies it as Normal time, Vacation, Holiday , or Other.

In a perfect world I would just be able run the reports for each week separately, calculate the "Normal" hours worked, and if it is greater than 40, determine the hours for overtime. But, these reports will usually be run for TWO week periods at a time. How do I know what the total was for week1, week2, etc.

I will stop here so I don't cloud the question any further. Please Help!!

[jzwp11]

You will need the datepart() function which can be used to determine the week number for a given date

datepart("ww",yourdatefield)

You can then use an aggregate query to group by week number and sum the hours accordingly You will probably have to take into account the year somewhere in there. The datepart() function has a couple optional parameters that you may need, so be sure to check out the help section.

[Mclaren]

you will also need to have a table consisting of Holiday dates, and depeneding on the laws in your country, a table listing the dates the employee is on vacation (some countries don't pay vacation time). Also what about other types of leave ? Sick leave, Family responsibilty, study, maternity/paternity ?

These will need to be brought into your calculations.

[dustmopheadmop]

I think you have a problem with your table design that should include at minimum the following fields:
LngSignIn- autonumber
intEmp-Number
intYear -number
intWeek –number
DateTimeIn –DateTime-General Date
DateTimeOut-DateTime-General Date

Once you have done your table as mentioned, it will be very easy to query it with any calculation method in your mind and in any display you like.
That because you have a column for year, Week number and that is the building blocks of time keeping dbs.
I hope it help.