How to Calculate Gross Pay and Overtime in Excel

How to Calculate Gross Pay and Overtime in Excel
••• AndreyPopov/iStock/GettyImages

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.

Example of Excel Timesheet With Gross Pay and Overtime 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.