We frequently need to calculate the wages of the employees on a yearly, monthly, and daily basis. To make a sheet format to compute daily wages, Excel is the most commonly used application worldwide. In the following article, we are going to learn some easy and quick steps to create a daily wages sheet format in Excel.
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 this article, we’ll go through 7 simple and quick procedures for creating a daily wages sheet format in Excel. Use these techniques to quickly construct a daily wage structure.
In the following data set, we have In Time and Out Time of some employees of a company. Their hourly wage is also given. We need to calculate their daily wages based on their working times. Let’s proceed one step at a time.
Step 1: Calculate Total Daily Working Time in Daily Wages Sheet Format in Excel
To calculate the total daily Working Time, we need to subtract the In Time from Out Time. We also need to subtract 1 hour for Lunch Break.
We can 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.
Now drag the Fill Handle up to cell G12. Then we will get the rest of the working time for other employees.
In this step, our goal is to determine whether an employee has completed his regular 8 hours of working time or less. If an employee has worked more than 8 hours; For example 10 hours, in this cell 8 hours will be recorded. His extra 2 hours of work will go to the Over Time section.
We are going to use an IF function here. The logic is if the Working Time is less than the Standard Working Hours return the Working time to the cell. If this is not true then it means the employee has worked 8 hours or more. So it will return 8 hours if the condition is not true.
We can use the following formula in cell H7.
Here, G7 represents starting cell of Working Time and I4 refers to the value of standard working hours.
By dragging the Fill Handle we will get Regular Working Time for the rest of the employees.
Read More: Per Day Salary Calculation Formula in Excel
Step 3: Calculate Daily Over Time
We can calculate Over Time (OT) by subtracting Regular Working Time from Working Time. Here we are going to use the IF Function again. The logic is if the difference is greater than 0 it means the has worked Over Time (OT). But if it is less or equal to 0, it means the employee has not worked any Over Time (OT).
We can use the following formula in cell I7 to calculate Over Time (OT).
In this formula, H7 represents the cell of Regular Work Time.
Afterward, drag the Fill Handle and you will get Over Time (OT) for the remaining employees.
Step 4: Determine Over Time Wage Rate in Daily Wages Sheet Format in Excel
Here we assumed the OT wage rate is 1.5 times the Regular Wage/ Hour. So, to determine OT Wage/ Hour we need to multiply Regular Wage/ Hour by 1.5.
By using the following formula in cell E15 we can get the OT Wage/ Hour.
Here, D15 refers to the cell of Regular Wage/ Hour.
After that, just drag or double-click the Fill Handle to get the rest of the data.
To compute Regular Hours’ Payment we need to multiply the Regular Work Time by the Regular Wage/ Hour. But here Regular Work Time is in time format. We need to convert it into a number format data. For this, we are going to use the HOUR function of Excel.
We can apply the formula given below in cell F15 to compute Regular Hours’ Payment.
Now use the Fill handle so we can get the remaining data.
To calculate OT Payment we need to multiply OT Wage/ Hour and Over Time (OT). Again we are going to use the HOUR Function to convert Over Time hours to number format.
We can use the following formula in cell G15 to calculate the Daily Over Time Wages.
In this formula, E15 refers to the cell of OT Wage/ Hour and I7 represents the cell of Over Time (OT).
Afterward, double-click or drag the Fill Handle. Then you will get OT Payment for all the employees.
Step 7: Determine Daily Net Wages in Daily Wages Sheet Format in Excel
Finally to determine the Net Payment, simply add the Regular Hours Payment and OT Payment.
By using the following formula in cell H15, we can determine Daily Net Wages.
Here F15 describes the cell of Regular Hours’ Payment and G15 refers to the cell of OT Payment.
Now by using the Fill Handle we can get the Net Payment for the rest of the employees.
Things To Remember
- In Step 2, while writing the formula, make sure that you choose Absolute Cell Reference for cell I4.
- In Step 5 and Step 6, use the HOUR function before multiplying it with the wages rate. Otherwise, you will get weird and wrong outputs.
Download Practice Workbook
Thank you for reading through this post. I sincerely hope that this tutorial has helped you construct a Daily Wages Sheet Format in Excel. If you have any questions, please leave them in the comments section.
- How to Create Salary Breakup Calculator in Excel
- How to Make a Future Salary Calculator in Excel
- TDS Deduction on Salary Calculation in Excel Format
- Attendance Sheet with Salary in Excel Format
- Create a Monthly Salary Sheet Format in Excel
- How to Create Salary Slip Format with Formula in Excel Sheet
- Create Tally Salary Slip Format in Excel
- Driver Salary Slip Format in Excel
- How to Create Automatic Salary Slip Generator Using Excel