Attached is an excel spreadsheet that’s created from an access database. It collects badge-ins and badge-outs for all employees and contractors in the facility.
I need a VBA macro created that first looks at the time an employee works (thedifference between the badge-in and badge-out) and determines whether they are working 8, 10, or 12 hour schedules. Then I need it to determine if the employee is working day or night shifts. Next based on the schedule and shift I need it to count the consecutive days worked based on the date, and the number of hours worked based on the time. Finally I need a new sheet created that list employees who exceed the established rules.
Definitions:
-A workset is a string of consecutive days worked. The workset ends once an employee has 35 hours off.
-If you badge-in after midnight then you are on day shift. If you badge in after noon then you are on nights.
Rules:
-Employees may not work more than 17 hours in a 24 hour period.
-If you work 13 hours or more in a 24 hour period then you must have atleast 7 hours off before returning to work.
12 hour shifts:
-Employees may work no more than 1 shift of 17 hours in a 24 hour period
-Employees may work no more than 14 consecutive days in a workset
10 hour shifts:
-Employees may work no more than 1 shift of 17 hours in a 24 hour period
-Employees may work no more than 14 consecutive days in a workset
8 hour shifts:
-Employees may work no more than 2 shift of 17 hours in a 24 hour period
-Employees may work no more than 19 consecutive days in a workset
