September 22, 2017
My name Shazia and I'm new to the forum and MS Excel 2016.
I'm looking to find help with creating a timesheet that calculates different pay rates for shift work.
I employ a number of personal care assistants (PA) to assist me in my daily life. I have to maintain a number of records, timesheets are one of them.
There are 2 different shift my staff work, day and night shift. Day shift starts from 8:00am to 11:00 pm, night shift starts from 11:00 pm to 8:00 am. They are paid a different rate for each, £9 for day and £10.50 for night. On bank holiday the rate is doubled.
The timesheet needs to show the date, start and finish time, hours worked, bank holiday hours, sick hours, annual leave hours and the total pay.
I'm using MS Excel 2016 for Mac and am a complete novice. I've been trying to teach myself using books and the internet. I've come across lots of excel timesheet templates free on the web but I've not found anything that meets my exact needs.
I did however found one tutorial on creating an Excel Timesheet with Different Rates for Shift Work and used this as a base to work from.
This timesheet does the following:
- There are two different shifts day and night – Day and night shifts are entered on separate rows.
- Logs-in and logs-out date and time of shift.
- Timesheet needs to differentiate between day and night shift. A column (column E) is also used to record whether it is a day or night shift.
- Calculates hours if “day” shift. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.
- Calculate hours if “night” shift. The IF function first tests if it was a night shift, then test to see if it was worked overnight (A5<C5), and runs the correct calculation as a result.
- Calculates pay. 2 lookup tables are set up on a different sheet Range Names dayand night. One is for day shifts and the other for night shift. The final formula looks up the rate using the number of the weekday in the correct table. IF(E5=”Night”,G5*VLOOKUP(WEEKDAY(A5,2),night,2,FALSE),F5*VLOOKUP(WEEKDAY(A5,2),day,2,FALSE))
- The IF function tests to see if the night shift was worked. If so a VLOOKUP function is used to look up the required rate of pay in the night table, and this is then multiplied by the hours in cell G5. If they did not work the night shift, then a VLOOKUP function returns the rate from the day table and this is then multiplied by the hours in cell F5.
ADAPTION I WANT TO MAKE TO TIMESHEET:
As you can see the timesheet template does a lot of what I’m looking. I have to admit I can’t understand everything it does.
For example, the timesheet template uses a lookup table for the rate of pay in a week. I pay my staff the same rate all week it does not change at the weekend. There are only 2 different rates for day or night. Is there an easier way to do this?
I need to adapt the timesheet to do the following functions but I don’t know how. I would be really grateful for any help or advice on how to adapt my timesheet please.
- Create weekly timesheet for each carer.
- Record bank holiday hours and pay. On bank holidays the pay rate is doubled both day and night. Bank holiday day rate £18, bank holiday night rate £21.
- Show sick leave hours.
- Show annual leave hours.
- Total hours.
- Total pay.
- Need to record the totals for each week on to a different monthly summary table showing totals for hours worked, sick hours, annual leave hours, bank holiday hours and total pay.
If you can help me in anyway with the adaptions I will be externally grateful. I’ve tried giving as much detail as possible (maybe too much) to help you understand what I’m doing. I'm a beginner and I really want to learn but I need some help.
I've attached 2 file one shows the working timesheet the other is a test version that I've used to add bank holiday hours but does not work. Feel free to look at the file and suggest changes I could really do with the advice because I have not got a clue.
PLEASE PLEASE HELP
June 25, 2016
Please refer to the attachment. Not sure if it will work on Excel 2016 for Mac.
1) I have added Data Validation in column E so that the rate can be selected (prevent keying error)
2) I have added a column F (Rate) so that you can see the rate. You can combine the formula in this column with the Earned column if you don't want to see the rate.
Hope this helps.
September 22, 2017
Thanks for your reply I really appreciate it.
I had a look at what you did and it's working great just what I was looking for.
As I am a complete beginner to Excel please could you talk me through what you did and how it works step by step, I really want to learn.
How did you do the drop-down list for shift type? And how does it work?
I was wondering if you could add "Sick Leave" and "Annual Leave Day" and "Annual Leave Night" (as annual leave is paid at day and night rate) to the drop-down list. Then when they are chosen the hours are entered into Sick or Annual Leave column and not Total Hours.
June 25, 2016
I am using Data Validation to create the drop-down list. You can learn about it here https://www.myonlinetraininghub.com/excel-drop-down-lists
Whenever you select a shift code, I use VLOOKUP to get the rate from the Rate Of Pay worksheet.
To determine where the Sick and Annual Leave hours is to be placed, I use the LEFT function to check the shift code.
For the Working Hours, I am checking if both the Sick and Annual Leave Hours is equal to 0. If they are then it must be Working Hours.
Hope this helps.
Most Users Ever Online: 57
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas