Daily Wages Sheet Format in Excel (with Quick Steps)

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.


Download Practice Workbook


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.


7 Steps to Create a Daily Wages Sheet Format in Excel

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.

7 Steps to Create a Daily Wage Sheet Format in Excel


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.

=(E7-D7)-F7

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

Calculate Total Daily Working Time in Daily Wages Sheet Format in Excel

Now drag the Fill Handle up to cell G12. Then we will get the rest of the working time for other employees.

Calculate Total Daily Working Time in Daily Wages Sheet Format in Excel

Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)


Step 2: Determine Daily Regular Work Time in Daily Wages Sheet Format in Excel

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.

=IF(G7<$I$4,G7,$I$4)

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

Determine Daily Regular Work Time in Daily Wages Sheet Format in Excel

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

Determine Daily Regular Work Time in Daily Wages Sheet Format in Excel

Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)


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).

=IF(G7-H7>0,G7-H7,0)

In this formula, H7 represents the cell of Regular Work Time.

Calculate Daily Over Time

Afterward, drag the Fill Handle and you will get Over Time (OT) for the remaining employees.

Calculate Daily Over Time


Similar Readings


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.

=D15*1.5

Here, D15 refers to the cell of Regular Wage/ Hour.

Determine Over Time Wage Rate in Daily Wages Sheet Format in Excel

After that, just drag or double-click the Fill Handle to get the rest of the data.

Determine Over Time Wage Rate in Daily Wages Sheet Format in Excel


Step 5: Compute Daily Regular Wages in Daily Wages Sheet Format in Excel

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.

=D15*HOUR(H7)

Compute Daily Regular Wages in Daily Wages Sheet Format in Excel

Now use the Fill handle so we can get the remaining data.

Compute Daily Regular Wages in Daily Wages Sheet Format in Excel


Step 6: Calculate Daily Over Time Wages

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.

=E15*HOUR(I7) 

In this formula, E15 refers to the cell of OT Wage/ Hour and I7 represents the cell of Over Time (OT).

Calculate Daily Over Time Wages 

Afterward, double-click or drag the Fill Handle. Then you will get OT Payment for all the employees.

Calculate Daily Over Time Wages 


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.

=F15+G15 

Here F15 describes the cell of Regular Hours’ Payment and G15 refers to the cell of OT Payment.

Determine Daily Net Wages in Daily Wages Sheet Format in Excel

Now by using the Fill Handle we can get the Net Payment for the rest of the employees.

Determine Daily Net Wages in Daily Wages Sheet Format in Excel


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.

Conclusion

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. For more Excel tutorials, please visit our website ExcelDemy. Happy learning!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo