New Perspectives Excel 365

New Perspectives Excel 365 | Module 3: End of Module Project 1 Jiang Engineering

PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS

September 7, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file NP_EX365_EOM3-1_FirstLastName_1.xlsx as NP_EX365_EOM3-1_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_EOM3-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Sergio Cardona is a project manager for Jiang Engineering in San Bernadino, California. In addition to permanent employees, the company often hires professional engineers for the short term on an hourly basis. In an Excel workbook, Sergio is preparing a new time sheet and paid leave tracker for the hourly employees and asks for your help in performing the calculations. Go to the Weekly Time Sheet worksheet. In cell I5, insert the TODAY function to record the current date.

2. Use the value in cell B9 to fill the blank row headings with the remaining days of the week.

3. Sergio wants you to enter formulas to calculate the number of hours Mia Hakim worked each day and for the week. In cell G9, insert a formula without using a function that calculates the total hours worked. Add the difference of the first time out (cell D9) and the first time in (cell C9) to the difference of the second time out (cell F9) and the second time in (cell E9), and then multiply the result by 24 to find the total hours. Copy the formula in cell G9 to the range G10:G15 to calculate the total hours for each day.

4. In cell H9, enter a formula using the IF function to test whether the total hours for the day (cell G9) are greater than 8, the number of regular work hours. If the total hours are greater than 8, display 8 as the number of regular hours in cell H9. Otherwise, display the total hours (cell G9). Copy the formula in cell H9 to the range H10:H15 to calculate the regular hours for each day.

5. In cell I9, enter a formula using the IF function to test whether the total hours for the day (cell G9) are greater than 8. If they are, subtract 8 from the total hours (cell G9) to display the number of overtime hours in cell I9. Otherwise, display 0. Copy the formula in cell I9 to the range I10:I15 to calculate the overtime hours for each day.

6. In cell G16, enter a formula using the SUM function to calculate the total hours (the range G9:G15). Fill the range H16:I16 with the formula in cell G16 to calculate the total regular and overtime hours for the week, filling without formatting.

7. The pay rates depend on the employee's position. In cell H17, enter a formula using the XLOOKUP function that looks up the position (cell I6), uses the list of positions (the range B21:B25) as the lookup array, and uses the pay rate for regular hours (the range C21:C25) as the return array to find the appropriate regular hourly rate.

8. In cell I17, enter a formula using the XLOOKUP function that looks up the position (cell I6), uses the list of positions (the range B21:B25) as the lookup array, and uses the pay rate for overtime hours (the range D21:D25) as the return array to find the appropriate overtime hourly rate.

9. In cell H18, calculate the total regular pay by multiplying the regular hours for the week (cell H16) by the rate for regular hours (cell H17) to find the total pay for regular hours. In cell I18, calculate the total overtime pay by multiplying the overtime hours for the week (cell I16) by the rate for overtime hours (cell I17) to find the total pay for overtime hours.

10. In cell J18, enter a formula using the SUM function to calculate the total pay (the range H18:I18).

11. Sergio also wants you to display statistics for hours worked. In cell I21, enter a formula using the AVERAGE function that calculates the average hours for days worked (the range G9:G13). In cell I22, enter a formula using the MIN function that calculates the minimum hours for days worked (the range G9:G13). In cell I23, enter a formula using the MAX function that calculates the maximum hours for days worked (the range G9:G13).

12. Go to the Leave Calculator worksheet. Sergio uses this worksheet to determine whether temporary employees are eligible for a paid leave, which they are if they have worked for at least 10 weeks. In cell E8, enter a formula using the ROUND function that subtracts the employee's start date (cell D8) from the report date (cell C5) to determine the number of days of employment, divides the result by 7 to determine the number of weeks of employment, and then rounds the value to 1 decimal place. Use an absolute reference to the report date (cell C5), and then fill the range E9:E20 with the formula in cell E8 to calculate the weeks employed for each employee.

13. In cell F8, enter a formula using the IF function that tests whether the number of weeks employed (cell E8) is greater than or equal to the weeks for eligibility (cell H5). If it is, display "Yes" in cell F8; otherwise, display "No" in cell F8. Use an absolute reference to the weeks for eligibility (cell H5), and then fill the range F9:F20 with the formula in cell F8 to determine whether each employee is eligible for a paid leave.

14. Sergio also wants to list the number of paid days for eligible employees. In cell H8, enter a formula using the IF function that tests whether the employee is eligible for a paid leave (F8="Yes"). If the employee is eligible, use the XLOOKUP function to look up the type of leave requested (cell G8) using the list of leave types (the range J8:J11) as the lookup array and the list of paid days (the range K8:K11) as the return array. If the employee is not eligible, display 0. Use absolute references to the list of leave types (the range J8:J11) and the list of paid days (the range K8:K11), and then fill the range H9:H20 with the formula in cell H8 to calculate the number of paid leave days for each employee.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

The value in cell I5 of the Weekly Time Sheet worksheet has been intentionally blurred as it will never be constant.

Final Figure 1: Weekly Time Sheet Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Leave Calculator Worksheet

Need help with this project?