Awarding overtime based on hours and requests

Johnny00

New Member
Joined
Oct 1, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I am trying to automate filling open overtime slots based on a couple criteria. Employees are sorted based on their current month overtime hours worked, then their previous month overtime hours worked. Employees also put in weekly requests for overtime shifts for Mid, Day, or Eve shift. The employee with the lowest hours gets their first choice, then the hours are updated. Then the new employee with the lowest hours gets their first choice, and so on. If an employee has the lowest hours worked but didn't request any overtime, then they get skipped and the next in line gets their first choice. If an employee requested overtime for a shift that doesn't need any more workers, that employee gets their second choice of shift.

The file "Employee List" shows the overtime hours list next to the overtime request list. The file "Roster" shows the overtime "OT" slots that need to be filled.

The current system uses a printed form from an Access database to show each employee's current and previous overtime hours worked. Then a google form is used to record the overtime shift requests. And a shared Excel workbook contains a sheet for each day of the week that needs to be populated with the winners of overtime requests. Any help on automating this process would be greatly appreciated.
 

Attachments

  • Roster pic.PNG
    Roster pic.PNG
    55.9 KB · Views: 17
  • Employee list pic.PNG
    Employee list pic.PNG
    45.8 KB · Views: 16

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top