• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / How to create an Excel Timesheet with Different Rates for Shift Work|General Excel Questions & Answers|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…How to create an Excel Timesheet wi…
sp_PrintTopic sp_TopicIcon
How to create an Excel Timesheet with Different Rates for Shift Work
Avatar
Shazia Rashid

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 22, 2017
sp_UserOfflineSmall Offline
1
September 26, 2017 - 2:10 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
September 26, 2017 - 11:30 am
sp_Permalink sp_Print sp_EditHistory

Hi Shazia

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.

Sunny

Avatar
Shazia Rashid

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 22, 2017
sp_UserOfflineSmall Offline
3
September 27, 2017 - 2:22 am
sp_Permalink sp_Print

Hi SunnyKow

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.

Thanks again

Shazia

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
September 27, 2017 - 10:51 am
sp_Permalink sp_Print

Hi Shazia

I am using Data Validation to create the drop-down list. You can learn about it here https://www.myonlinetraininghu.....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.

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Jose Molina, Dieneba NDIAYE
Guest(s) 116
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Richard Adams
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31571
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.