Hello Everyone!
Who is up for an excel brain teaser?! :P
I have a troubling problem for my boss regarding employee timesheets and Queensland, Australia’s Enterprise Bargaining Agreements.
Problem:
Develop a spread sheet that my boss can input multiple employees’ daily start a and finish hours spent working per job code and reflect their hours based on:
1. Standard (100% of pay rate): Based on total hours worked in a day up to 8hrs (but allow for a 30min lunch break bringing it to 7.5hrs)
2. Over Time (150% of pay rate): Based on total hours worked in a day over 8hrs for a maximum of 2hrs
3. Double Overtime (200% of pay rate) Hourly Rates: Based on total remaining hours worked in a day over 10hrs.
Additionally, employees receive a mandatory 30min break between 12pm – 12:30pm which reduces their hours worked during that time by 30mins.
IE: Say an employee can work 8hrs on JOB 1, and 2 hours on JOB 2. In this case JOB 1 would have 7.5 standard hours worked and JOB 2 would have 2 over time hours worked.
See the attached spreadsheet for the layout and some example timesheet entries and their formula fields.
Setup:
Currently the spreadsheet’s user input columns set up like this:
Date | Employee Name | Job Number | Start Time | Finish Time | Comments
Note: The boss will input timesheet dates on a chronological basis as you go down the rows, ie: Dates and Times will increase and Job Numbers may jump all over the place.
The following columns require formulas to automatically divide the time (located to the right of the columns above):
Total Hours Worked | Standard Hours | Overtime Hours | Double Overtime Hours
Examples:
To get your head around some of the situations that may arise from hours worked on any one day, here are the following examples of columns split by a tab:
Date Name Job Start Finish Total Standard (First 8hrs) Over Time(Max. 2hrs) Double Overtime
18/4/15 Joe 1 6:00 9:00 3 3
18/4/15 Joe 4 9:00 LUNCH 13:00 4 3.5
18/4/15 Joe 2 13:00 17:00 4 0.5 2 0.5
In the above example, 3 hours are worked in the morning on standard rates, 4 hours are worked on Job 4 but the 30min lunch break is deducted making it 3.5hrs, 4 hours are worked on Job 2 of which, 0.5hrs is put to standard hours to reach the max hours at standard pay to 8hrs, 2hours to overtime which is the maximum for that pay rate and the remaining 0.5hrs to double overtime for the day.
Finally, I need all cells to be “” nothing if no data is placed into the date, employee name, job number, start and finish line.
Challenge:
I Timesheet Problem.xlsxu all in advance, would love to hear you solutions or just general feedback on how you would solve this excel problem.
Regards,
Michael
Bookmarks