How to create an Excel Timesheet with Different Rates for Shift Work|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi All,

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.

ADAPTIONS NEEDED:

• 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

Shazia