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!!