Requirements of The Project

Calculate over time and find out the Wage sheet in excel based on these requirements. Write formulas and functions as per conditions.

Prepare a Wage sheet following the condition is given below.

a) The working hours are total 8hr. If this time is more than 8 hr, then the time will be considered as overtime.

b) Over time per hr. $100.

c) Working hours per hr. $40.

d) Now find the Total Wage.

Solution

To calculate wage sheet in excel workbook, write down the Employee Name and Working hour of each employer’s in A and C column. Suppose the Data are in the screenshot.

how to make wage sheet

wage sheet

Finding over Time

Apply this rule in the C2 cell. We’ll use the IF function to solve this problem.

=IF(B7>8,B7-8,0)

overtime in excel sheet

overtime in the excel sheet

If B7 that means working hour is more than 8 hr, it’ll be minus 8 from the total working hour. It is not more than 8 hr then it is 0.

Apply this rule in every cell in the overtime column

To apply in every cell keep press “Fill Handle” under selection takes to C15 cell.

Finding Total wage

Apply this rule in D2 cell. We use the IF function to solve this problem.

=IF(B7>8,C7*100+8*40,B7*40)

how to make wage sheet

how to make wage sheet

We’ll calculate working hour $40 per hr and overtime $100 per hour.

Apply this rule in every cell of the wage sheet column.

To apply in every cell, keep press “Fill Handle” under selection takes to D15 cell.

Share To Care…

Learn More…

Subscribe Me To Get Newsletters...