Question

Prepare a worksheet that allows the user to enter ONLY these fields: Start Time and End Time per day on a 5 day workweek (10 cells) and Hourly Wage (1 cell), Total Hours Worked (1 cell), and Total Earnings (1 cell). So there should ONLY be 13 data entry fields for the user.  Each of those fields should have a title, so there should be an additional 13 title cells explaining each data cell. Make sure that the title fields are properly labeled. So, there should be 26 cells used in total. 

The user will earn time and a half of the wage entered for the hours worked above 40 in a 5 day week. So if the user earned $10/hour and worked a total of 30 hours, the weekly earnings would be $300 (= $10/hours x 30 hours). However, if the user entered an amount over 40, the hourly wage for the overtime hours should be time and a half (which is 150% of the regular rate) calculated at $10 x 1.5 = $15/hour. If the user worked 45 hours, the user would show earnings of $475 (=(40 hours x $10/hour) + (5 hours x $15/hour)).

So

Your output cells should show:

1) the total amount hours worked and

2) the total weekly of earnings based upon the start and end times from Monday through Friday and hourly rate.

The output for weekly earnings must use a single cell. All calculations MUST appear within that SINGLE output cell. You are not to add any more calculation cells than the ones described. You must use an IF function to calculate the output for weekly earnings. 

Hint:

IF the worker entered 40 hours or less, calculate wages based on the regular pay rate only.

IF the worker entered more than 40 hours, there are 40 hours calculated at the regular rate PLUS the amount of hours above 40 at a rate 150% (or 1.5) times the regular rate. 

Answer 1

Follow the below steps to prepare the worksheet as follows:

Start by entering the details given in the problem

Now enter a simple addition and subtraction formula in order to find out total hours worked.

Now calculate the weekly earnings using the below formula:

=IF(B7>40,B6*1.5*(B7-40)+40*B6,B7*B6)

Now right click and go to format and click on protect to lock the cells.

Now go to review on the toolbar and click on protect sheet.

THe resultant worksheet is as under: