Excel formulas are valuable tools for tracking gross and overtime pay. Whether recording your own working hours or managing payroll for a small business, setting up a Microsoft Excel workbook can streamline the process and minimize errors. You need to know the hourly rate, overtime rate and hours worked to create Excel formulas to calculate gross pay and overtime pay at the touch of a button.
What Is Gross Pay?
Gross pay is total earnings before payroll deductions, taxes and benefits.
For a salaried employee, gross income is equal to the total annual salary divided by the number of pay periods in the year. For instance, if your annual salary is $52,000 a year and you are paid 26 times a year, your gross pay is $2,000 per pay period. However, for an hourly employee, gross pay is the hourly rate multiplied by the number of hours worked.
What Is Overtime Pay?
Overtime pay is a higher compensation rate for eligible employees who put in over 40 total working hours in a standard workweek. According to the U.S. Department of Labor and the Fair Labor Standards Act (FLSA), unless an employee is exempt, they must receive overtime pay at a rate of at least 1.5 times their regular pay rate for overtime hours.
The FLSA and DOL offer specific outlines, definitions and general guidance regarding overtime pay for employees and employers.
How to Use Excel Formulas to Calculate Pay
As with any workbook in Excel, you will use a series of columns. Some columns will be fields where a constant is entered, such as an employee’s hourly rate. Other columns will contain formulas to perform calculations. Whether using a premade Excel template from Microsoft or building it yourself, you must be familiar with the following formulas.
Basic Formula for Gross Pay
For regular gross pay without overtime, multiply the regular pay rate by the number of hours worked. If the pay rate is $20 per hour and an employee has worked 30 total hours, gross pay is $600.
Basic Overtime Calculation
Overtime pay is calculated just like gross pay, but the regular pay rate must first be multiplied by 1.5 to get the overtime rate. Then, multiply the overtime rate by the hours worked.
If regular pay is $20 per hour, the overtime rate is $30 for each hour over the standard 40-hour workweek. The overtime rate is only paid on the amount of overtime, not regular working hours.
If an employee works 50 hours, the first 40 are calculated at the regular pay rate. The overtime pay rate of $30 would apply to the 10 hours of overtime. In this example, $300 of overtime pay is added total regular pay to get the employee's total payment for the workweek.
How to Set Up an Excel Worksheet as a Timesheet
The steps to set up and build your sheet follow below. If you need additional help with formatting or formula creation, Microsoft offers tips and tutorials to help.
Name the Columns
- Column A: Employee name
- Column B: Time in
- Column C: Time out
- Column D: Total work time
- Column E: Regular time
- Column F: Overtime hours
- Column G: Regular rate
- Column H: Overtime rate
- Column I: Total regular pay
- Column J: Total overtime pay
- Column K: Gross pay
Set up the Formulas and the Constants
Setting up the sheet will involve using a mixture of constants and formulas. Note that you will be using decimals for all your entries instead of fractions.
- Column A: Enter an employee's name on each row.
- Columns B and C: Enter the employee's start times and end times, using the 24-hour clock.
Note: To choose 24-hour time, highlight columns B and C, right-click and choose "Format cells." Choose "Custom" and select "h:mm" from the dropdown menu, explains Microsoft. - Column D: (formula) =(C2–B2)*24
- Column E: (constant) Enter 8.00
- Column F: (formula) =D2–E2
- Column G: (constant) Enter the employee's regular pay rate.
- Column H: (formula) =G2*1.5
- Column I: (formula) =G2*E2
- Column J: (formula) =H2*F2
- Column K: (formula) =J2+I2
If meal breaks are not paid, account for this time difference in Column E. For instance, to account for a half-hour unpaid lunch break, enter 7.5 as the regular work time.
Copy the Excel Formulas for Every Row
Once the master formulas are set up in the top row, select the first cell in each row with a formula (D, F, H, I, J and K) and copy and paste the formula down each row.
Enter Your Payroll Information
When you enter the timesheet information for each employee, the regular pay and overtime pay information will calculate as you go, doing the math for you and arriving at your totals for regular pay, overtime pay and gross pay.
Name | Time in | Time out | Total work time | Regular time | Overtime | Regular rate | Overtime rate | Regular pay | Overtime pay | Gross pay |
---|---|---|---|---|---|---|---|---|---|---|
Pat | 8:00 | 20:15 | 12.25 | 8.00 | 4.25 | $15.00 | $22.50 | $120.00 | $95.63 | $215.63 |
Sam | 8:00 | 20:15 | 12.25 | 8.00 | 4.25 | $16.25 | $24.38 | $130.00 | $103.59 | $233.59 |
Jan | 8:00 | 20:15 | 12.25 | 8.00 | 4.25 | $20.00 | $30.00 | $160.00 | $127.50 | $287.50 |
Common Questions About Overtime Pay
Who Gets Overtime Pay?
All employees not exempt from FSLA rules must receive overtime pay for working more than 40 hours in a workweek. Exempt workers include salaried employees or those who otherwise fit the FSLA standards of an exempt employee.
Do I Get Overtime Pay if I Work More Than 8 Hours Per Day?
The FLSA strictly defines a workweek as 168 consecutive hours – seven consecutive 24-hour periods. According to FSLA standards, working more than eight hours daily would not qualify for overtime. However, some states, such as California and Alaska, implement additional rules regarding overtime and the length of a single workday.
Is Double-Time Different Than Overtime?
While the FLSA mandates rules for one and one-half times an employee's regular pay rate after 40 hours in a single workweek, the DOL clarifies there is no such requirement for double-time pay. Paying double time on holidays or other reasons is at an employer's discretion.
References
- U.S. Department of Labor: Wages and the Fair Labor Standards Act
- U.S. Department of Labor: Overtime Pay: General Guidance
- Microsoft: Time Sheet (worksheet)
- Microsoft: Excel video training
- Microsoft: Format numbers as dates or times
- DOL: Fact Sheet #17A: Exemption for Executive, Administrative, Professional, Computer & Outside Sales Employees Under the Fair Labor Standards Act (FLSA)
- California.gov: Overtime
- Alaska.gov: Wage and Hour - Employees' Frequently Asked Questions
Tips
- If you worked 40 hours or less in a week, skip Steps 2, 4 and 6 because you do not have any overtime for that week.
Writer Bio
Melissa is a writer and editor from Chicago, with a background in small business ownership. After selling her business, she moved into marketing for nonprofits and now manages volunteers at a large medical association. She is a writing and editing contractor and contributed to dozens of blogs and websites.