Daily Wages Sheet Format in Excel (with Quick Steps)

What Is a Wages Sheet?

A wages sheet is a human resources document used by an organization to determine the wages of its employees. It is often referred to as payroll or payroll sheet. Every employee receives a wages sheet after each pay period, which summarizes the total amount they owe their employers for the work they’ve completed during that time period.

Create a Daily Wages Sheet Format in Excel: 7 Steps

In the following data set, we have the Clock In Times and Clock Out Times of some employees of a company. Their hourly wages are also given. We need to calculate their daily wages based on their working times.

Step 1 – Calculate the Total Daily Working Time in the Daily Wages Sheet

To calculate the total daily Working Time, subtract the In Time from Out Time. We also need to subtract 1 hour for the Lunch Break.

Use the following formula in cell G7 to determine the total Working Time:

Here E7 is the cell of In Time, D7 is the cell of Out Time and F7 is the cell of Lunch Break time.

• Drag the Fill Handle to cell G12.

Step 2 – Determine the Daily Regular Work Time

We will separate the working time into regular 8-hour shifts and overtime.

• Use the following formula in cell H7.

G7 represents starting cell of Working Time and I4 refers to the value of standard working hours.

• By dragging the Fill Handle down, we will get Regular Working Time for the rest of the employees.

Step 3 – Calculate Daily Overtime

• Use the following formula in cell I7 to calculate Overtime (OT).

• Drag the Fill Handle and you will get Over Time (OT) for the remaining employees.

Step 4 – Determine the Overtime Wage Rate

Let’s assume the OT wage rate is 1.5 times the Regular Wage.

• Use the following formula in cell E15:

Range D15:D20 contains the hourly wage for each worker.

• Drag down or double-click the Fill Handle to get the rest of the data.

Step 5 – Compute the Regular Daily Wages

• Apply the formula given below in cell F15:

• Use the Fill handle so we can get the remaining data.

Step 6 – Calculate Overtime Wages

• Use the following formula in cell G15:

• Double-click or drag the Fill Handle to fill in the rest of the column.

Step 7 – Determine Daily Net Wages

• Insert the final formula in H15:

• Use the Fill Handle to AutoFill.

Things To Remember

• In Step 2, make sure to use an Absolute Cell Reference for cell I4.
• In Step 5 and Step 6, use the HOUR function for the time to convert it into a usable number. Otherwise, Excel will perform a different conversion that will lead to wrong results.

Download the Practice Workbook

<< Go Back to Make Salary Sheet | Salary | Formula List | Learn Excel