I really need to impress my boss for a promotion.
My document is messy so any help to make it look great would be helpful. Unfortunately my budget isn't that much.
My wishlist items are as follows:
1. The overtime calculations to be correct against the timesheet. Rules are in the OT_Code tab
2. Individual timesheets per staff member instead of everything on one sheet (can go up to 100 staff). Some VBA to make it hidden. the times are pulled through automatically from the Actual_Roster page by Vlookup on the Duty_Codes Page. Timesheets are only amended manually if someone is late. If not amended it sticks to default times.
3. A great dashboard that shows a whole bunch of stats, perhaps overtime, rostered shifts etc.
Currently number 1 is the priority.
Just to explain some things:
1. all staff names are pulled from the Staff_List sheet
2. All shift codes are pulled from the Duty_Codes sheet
3. All Overtime info is from the OT_Codes sheet
4. Actual_Roster is a duplicate of planned roster (changes made manually on actual_roster)
5. All dates are pulled from the Dashboard tab
6. My Hours Tab is for reference (I know my formulas are lengthy but I don't know excel well, but they seem to work). The labels are as follows:
*SH - Total Shift Hours
*BR - Total Break hours (1 every 5 hours)
*WD1 - Working hours (breaks have been removed) for day of shift start
*RH1 - Regular hours (04:00-21:00) for WD1
*NH1 - Night Hours (21:00-04:00) for WD1
*WD2 - Working hours (breaks have been removed) for next day if past midnight duty occurs
*RH2 - Regular Hours for day 2
*NH2 - Night Hours for day 2
I need the overtime calculations to go in the highlighted column under the Overtime Sheet. White column all hours passing 8 hrs that fall within (04:00-21:00) to go into this column. Dark grey column is for the night hours.
The tricky part is if someone is on a 12 hour shift and they finish at 22:00, 2 hours is overtime as a maximum of 8 hours is allowed with a 2 hour break. But 1 hour falls on the regular hours and one hour falls on the night. This has to be recorded accordingly.
Another example is if they finish a shift at 01:00 and have 3 hours overtime, 2 hours must be in the night range for day 1 and 1 hour must be in the night range for the next day.
I hope this will make sense to you.
NOTE: I know my date formula on my timesheet next to the individual times is incorrect. I'll fix that.
13 freelancers are bidding on average $46 for this job
I have read your job description and interested to deal with you. I am offering my service on short notice. Relevant skills and experience please consider me and give me a chance to impress you by my quality services.