How to keep track of payroll hours with an Excel Spreadsheet

time sheet


HR tech that powers small businesses. Get in, get the job done and keep your business moving with an easy to use solution for time, HR and benefits.

Updated December 30, 2020

How to keep track of payroll?

We get it. You’ve got employees and need to track their time, but you don’t really want to pour any money into any timekeeping software. That’s why we created Swipeclock’s employee timekeeping spreadsheet instructions. If you’re wondering how to keep track of payroll, here is a good start.

How to keep track of timesheets? We’ve got you covered with instructions for an employee timekeeping spreadsheet.

There are many benefits for employers to switch from a paper time card to an Excel time tracking spreadsheet.

5 Benefits of Using an Excel Spreadsheet Instead of Employee Time Cards

  1. The spreadsheet eliminates the hassle of trying to read or interpret employee time-punch data. Not only are clock-in and clock-out times available and easy to read, but break times and total shift hours are also easy to read.
  2. No more calling, or tracking down employees for missing or illegible information. An Excel spreadsheet quickly shows if a field is missing or absent. Employee income totals won’t populate if the employee is still clocked in or if they failed to clock in for a shift. That means that employees won’t miss a punch before turning in their time cards. This will save your payroll department processing time.
  3. Upgrading to an Excel Spreadsheet for your time tracking means that your physical time cards won’t get misplaced, misfiled, or lost. Did you know that on average it costs a business $139 to replace a missing document? When that lost document is a timecard, the cost could be much higher, especially if the document is discovered missing during a labor dispute or in the advent of an employee complaint.
  4. An up-to-date, accurate Excel spreadsheet can help with compliance. Did you know that most employment and labor laws are written to assume employer guilt when the employer has incomplete or missing documentation? That means that even a single missing timesheet could result in penalties for your business. At the very least, it will mean extra time and lost productivity while your employees track down and attempt to duplicate the information to the best of their abilities.
  5. You can copy and paste hours data into the payroll system instead of manually entering it. This will save some of the time that payroll employees spend processing payroll.

How to Use An Excel Spreadsheet to Keep Track of Employees’ Time

        1. Open an Excel Time Tracking Spreadsheet
        2. Import it into Google Docs if desired
        3. Prepare the spreadsheet for use
          1. Google Docs this is done through the SHARE button on the top right. Access can be shared for edit purposes and can be shared for view-only
          2. Link-based access should NOT be given as there will be no way to trace alterations to the spreadsheet
          3. Excel permissions can be assigned by a user
          • Populate company information and employee information
            1. Cell D3 should contain the employee’s name
            2. Cell D5 should have the first date of the pay period
            3. Cell D7 should contain the employee’s hourly rate
          • Set permissions for who has access to the spreadsheet
        4. Train employees on how to use the Excel timesheet
          • Employees should understand that breaks should be tracked and time accurately recorded
            1. Employee Location should be typed in on C14, C16, C18, C20, C22, C24, and C26
            2. Employee Clock in time should be chosen on D14, D16, D18, D20, D22, D24 and D26
            3. Employee lunch break clock out times should be recorded on E14, E16, E18, E20, E22, and E24
            4. Employee lunch break clock in times should be recorded on F14, F16, F18, F20, F22, and F24
            5. Employee shift clock out times should be recorded on G14, G16, G18, G20, G22, and G24
            6. Employee total regular hours will calculate in cells H14, H16, H18, H20, H22, and H24
            7. Any employee overtime hours will calculate in cells I14, I16, I18, I20, I22, and I24
            8. Employee pay for the day will calculate in cells J14, J16, J18, J20, J22, J24
          • Employees should be provided with the ability to ask questions and get any clarifications they need regarding the use of the spreadsheet.
        5. Troubleshoot Possible Errors
        • My Total Hours won’t calculate
          • Make sure that the clock in, break out, break in, and clock out times are all filled out accurately
          • Check that the clock in times doesn’t run over a 24 hours period. For example, if you started work at 8 am and are clocking out at 5 pm, make sure you haven’t input 5 am as your clock out time.
          • Make sure that your time is recorded on the same calendar day. An overnight shift of 11 pm to 5 am will cause issues as Excel timekeeping is not made to handle shifts covering more than a single calendar date
        • My Total Wages are wrong
          • Check that all clock in and out times are accurate
          • Check that the employee’s hourly wages are accurate in cell D7
          • Check that the employee’s overtime multiplier is accurate in cell D11

Watch Out For These Potential Timekeeping Issues

Using an Excel employee time tracking spreadsheet is a definite step up from manual time cards! However, there are a few things that you should be aware of. These can cost your company in extra wages, penalties, and disputes as you work out how to keep track of payroll.

The Excel Timekeeping Spreadsheet tracks time to the closest 30-minute increment.

That means that if your employees are 5 or 10 minutes late, they will still earn wages for the time they were late. This can cost your company money in unnecessary wage.

Human nature is to exaggerate in one’s behalf. That means that even honest employees tend to not notice exactly how late they were or how often they have been late. As a result, time theft is something that nearly all businesses deal with. On average, employers overpay employees an average of 4.5 hours each week. If you are using a clock in clock out spreadsheet to track time, it is important that you plan for those extra hours and understand that spreadsheets tend to average nearly twice as many excess wages as other higher-tech forms of timekeeping.

Retention guidelines and procedures are vital!

Since you are keeping your timecards in Excel, that means that it is up to your business to maintain permanent records of the employee’s time. You should have a system where you backup all your timesheets. Without this system in place, you are at risk of losing them.

That could happen if you get an angry employee who willfully deletes timekeeping records. It could happen with a server or desktop glitch or virus. It could happen with a physical emergency such as a fire, flood, hurricane, or other storm or natural disaster. Or it could happen with a technology failure. Be aware of these while planning how to keep track of payroll.

But no matter how it happens, your business is still held responsible for those records. The Department of Labor and all 50 state Departments of Labor or Workforce enforcements hold that a business is liable for missing employee records. Some states have extremely costly fines for missing documents. When you’re planning how to keep track of employees hours, pay attention to compliance.

Track Federal, Local, and State Labor and Employment Laws regarding employee timekeeping and payroll requirements.

Are you familiar with Federal meal Bbeak laws and the requirements for tracking and retaining timesheets? Don’t forget about your local state and city sick leave laws or secure scheduling laws.All of these laws require specific reporting, tracking, and retention requirements. In addition, if you have employees who work in multiple locations (Column C) make sure that you accurately calculate the minimum wage for each location that your employees work in. Some cities require a minimum wage for employees even if the business is not located within that city.

Once the employee has submitted the spreadsheet, make sure that you create a permanently locked file of the data.

This will help to ensure that the information cannot be altered or corrupted in the future. It will also help to ensure that questions of legality and accuracy don’t come into play. You can save the spreadsheet to a PDF for a more permanent copy or in Excel, you can permanently lock a specific sheet or an entire spreadsheet file.

Try TimeWorksExpress for Free!

Payroll automation in Excel is a good first step. Swipeclock’s TimeWorksExpress employee time track app is far easier and very cost effective. Try TimeWorksExpress for free. See how easy it can be to track employee time, PTO and create work schedules. After free trial, the cost is only $3 per employee per month. No contract required.

time clock hardware

What is the Best Employee Time Clock Hardware?

April 1, 2021
Posted in ,

What is employee time clock hardware? An employee time clock is a hardware device for tracking work time. To use it, employees ‘punch’ in/out or ‘clock’ in/out. The term ‘punch in’ stems from early employee time clock hardware that required a physical card. With a traditional mechanical time clock, the process from clocking to paycheck…

Read More
SwipeClock biometric employee time clocks

How Do Biometric Time Clocks Reduce Payroll Costs? [4 Simple Ways To Cut Overhead Without Layoffs]

March 11, 2021
Posted in ,

7 Minute Read Do you have hourly employees? Looking for ways to reduce labor costs without layoffs? Consider getting a biometric time clock. 4 Ways Biometric Time Clocks Reduce Payroll Costs Eliminate time theft by buddy punching Eliminate time theft by hours padding Eliminate out-of-schedule punches Prevent payroll mistakes Before we get into the details,…

Read More

WorkforceHub takes care of business.

We’ll show you how.

Request a Demo

brand - dots