How to keep track of payroll hours with an Excel Spreadsheet

Excel employee timekeeping

WorkforceHub

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.

Employee Timekeeping and Scheduling

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 timecard to an Excel time tracking spreadsheet.

5 Benefits of Using an Excel Spreadsheet Instead of Employee Timecards

  1. The spreadsheet eliminates the hassle of trying to read or interpret employee time data. See clock in, clock out, break times and total shift hours.
  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 timecards. This will save your payroll department processing time.
  3. Upgrading to an Excel Spreadsheet for your time tracking prevents your physical timecards from getting 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 audit.
  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 there is 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 time for your payroll manager.

How to Use an Excel Spreadsheet to Keep Track of Employee Time

Prepare the Spreadsheet

        1. Open an Excel Time Tracking Spreadsheet
        2. Import it into Google Docs if desired
        3. Prepare the spreadsheet for use
          • In Google Docs, do this through the SHARE button on the top right. You can share access for editing or for view only
          • Do NOT give link-based access as there will be no way to trace alterations to the spreadsheet
          • Assign Excel permissions
          • Populate company information and employee information
            • Firstly, Cell D3 should contain the employee’s name
            • Secondly, Cell D5 should have the first date of the pay period
            • Thirdly, Cell D7 should contain the employee’s hourly rate

Train Employees

        1. Instruct employees to accurately enter shift start, shift end, break start and break end
          • Enter employee location in C14, C16, C18, C20, C22, C24, and C26
          • Record employee clock in time should on D14, D16, D18, D20, D22, D24 and D26
          • Enter employee lunch break clock out times on E14, E16, E18, E20, E22, and E24
          • Record employee lunch break clock in times on F14, F16, F18, F20, F22, and F24
          • Enter employee shift clock out times on G14, G16, G18, G20, G22, and G24
          • Employee total regular hours will calculate in cells H14, H16, H18, H20, H22, and H24
          • Any employee overtime hours will calculate in cells I14, I16, I18, I20, I22, and I24
          • Employee pay for the day will calculate in cells J14, J16, J18, J20, J22, J24

How to Troubleshoot Errors

        1. 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 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.
        2. My Total Wages are wrong
          • Check that all clock in and out times are accurate.
          • Verify that the employee’s hourly wages are accurate in cell D7.
          • Check that the employee’s overtime multiplier is accurate in cell D11.

Using an Excel employee time tracking spreadsheet is a definite step up from manual timecards! But the system isn’t error-free. Problems 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 wages.

It’s human nature 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 employee time worked. 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. Furthermore, 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.

No matter how it happens, your business is still responsible for those records. The Federal Department of Labor and all 50 state Departments of Labor hold a business is liable for missing employee records. Some states have costly fines for missing documents.

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

Are you familiar with federal meal and break 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. 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.

WorkforceHub Makes it Easy

Payroll automation in Excel is a good first step. However, Swipeclock’s WorkforceHub is far easier and very cost effective. At $6 per employee per month, WorkforceHub will handle time tracking, scheduling, PTO and other HR functions. WorkforceHub also integrates with your payroll system. With WorkforceHub automation, you will never have to manually enter employee hours again. Request a WorkforceHub demo today.

Simplify HR management today.

Simplify HR management today.

HRIS System Evaluation Templates

HRIS Evaluation Template and Buyer’s Guide: Choose Your New Software

May 17, 2022
Posted in

Since a small business HR system has a lot of moving parts, you need an organized process to make an informed choice. An HRIS evaluation template can help. But first, let’s start with the basics of HRIS systems What is an HRIS? A Human Resources Information System (HRIS) is a type of business software. It…

Read More
hybrid workforce

5 Practical Tips for Managing Your Hybrid Onsite & Remote Workforce

April 19, 2022
Posted in

Here are 5 practical tips for managing hybrid teams (onsite and remote work). Being intentional about adapting for a distributed team will prevent lapses in productivity and engagement. Build a capable hybrid workforce that can drive your business forward post-pandemic. Practical Tip #1 Focus on outcomes for remote work It’s natural to worry that remote…

Read More
brand-workforce-shower

WorkforceHub takes care of business.

We’ll show you how.

Request a Demo

Looking for log-in help? Please reach out to your HR or payroll manager.

brand - dots