We will double click on the fill handle tool which is the small plus sign you see at the bottom right of Cell H4.Select and drag down to copy the formula to other cells.įigure 8: Overtime for Column F Calculating the Total Pay We will double click on the fill handle tool which is the small plus sign you see at the bottom right of Cell F4.Select and drag down to copy the formula to other cells.įigure 6: Regular time for Column E Calculating the Overtime We will double click on the fill handle tool which is the small plus sign you see at the bottom right of Cell E4.Select and drag down to copy the formula to other cells.įigure 4: Hours worked in Column D Calculating the Regular Work Time We will double click on the fill handle tool which is the small plus sign you see at the bottom right of Cell D4.We will insert the formula below into the cell.

To ensure that we do not encounter problems, we will convert the time from its native unit to decimal hours in Column D.

We will set up the data by inputting the Names, Time IN, Time OUT, and RATE for 7 employees in Column A, Column B, Column C, and Column G respectivelyįigure 2: Setting up the Data Calculating the Hours WorkedĮxcel calculates time as fractions of a day.

=(regular time*rate) + (overtime*rate*1.5) Formula The steps below will guide all levels of excel users through the process.įigure 1: How to Use Basic overtime calculation formula General Formula The result is how much PTO has been accrued in the pay period.We can use a set of formulas to calculate the pay associated with workers working overtime. With 0.08 as your multiplier, you can enter the days worked in the pay period and multiply them by 0.08. This means that for every day worked, the employee accrues 0.08 days off. If it is accrued hourly, you first need to divide the total number of vacation hours that they are entitled to in a year by the number of working days in the year.įor example, if an employee works 30 hours per week and there are 365 working days in the year, divide 30 by 365. If your employees are entitled to paid time off, then you likely have a system for accruing vacation time for every day or hour worked. To find net pay, subtract what is in the cell for total deductions from the cell with gross pay.

You can do the same for any other deductions and then add them together to calculate total deductions. For example, if you have gross earnings of $550 in column A1, and the income tax that must be deducted is 12%, you can enter =(A1)*0.12 in the cell that you would like the deduction to appear in. Deductions like income tax or health benefits can be calculated by subtracting their percentage from gross earnings.

Hourly wage calculator excel template