How to Calculate Gross Pay and Overtime in Excel

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.

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.

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.

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.

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.

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.

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.

• ‌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

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.

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.

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.

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.

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.

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.