$$ Open the file NP$\_$EX$19\_$EOM$3-1\_$FirstLastName$\_1.$xlsx$,$ available for download from the SAM website.

$$ Save the file as NP$\_$EX$19\_$EOM$3-1\_$FirstLastName$\_2.$xlsx by changing the $1$ to a $2.$

o If you do not see the $.$xlsx file extension in the Save As dialog box, do not type it$.$ The program will add the file extension for you automatically.

$$ With the file NP$\_$EX$19\_$EOM$3-1\_$FirstLastName$\_2.$xlsx still open, ensure that your first and last name is displayed in cell B$6$ of the Documentation sheet.

o If cell B$6$ does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

$1.$ Chanelle Larson is a professional assistant with Barrel Barnard Consulting. Chanelle is responsible for collecting consultant hours for some of Barrel Barnard's projects and processing the payments. She wants to automate some tasks and calculate summary information automatically on the worksheet that consultants use to track their hours.

Switch to the Timesheet worksheet. In cell C$5,$ insert the NOW function to record the current date.

$2.$ Each worksheet covers a period of $20$ work days. In cell C$4,$ create a formula using the WORKDAY function to calculate the date $19$ days from the start date $($cell C$3).$

$3.$ Use AutoFill to complete the labels in the table of hours. Use the values in the range A$9$:A$10$ to extend the week numbering to the range A$11$:A$12.$ Use the value in cell B$8$ to autofill the remaining weekday abbreviations in the range C$8$:F$8.$

$4.$ Add formulas to complete the table of hours used. In cell B$17,$ create a nested formula with the IF and SUM functions that check if the total number of hours worked in week $1($cells B$9$:F$9)$ is equal to $0.$ If it is$,$ the cell should display nothing $($indicated with two quote marks: $"").$ Otherwise, the cell should display the total number of hours worked in week $1.$ Copy the formula from cell B$17$ to fill the range B$18$:B$20.$

$5.$ In cell H$2,$ create a formula using the WORKDAY function to calculate the date $5$ days after the period end date $($cell C$4).$

$6.$ Create lookup functions to complete the summary section. In cell I$6,$ create a formula using the VLOOKUP function to display the number of hours worked in the selected week. Look up the week number in cell I$5$ in the range A$17$:G$20,$ and return the value in the $2$nd column. Use absolute references for cell I$5$ and the range A$17$:G$20.$

$7.$ Copy the formula from cell I$6$ to the range I$7$:I$11$ and then edit the formula in cell I$7$ to return the value in the $3$rd column, the formula in cell I$8$ to return the value in the $4$th column, the formula in cell I$9$ to return the value in the $5$th column, the formula in cell I$10$ to return the value in the $6$th column, and the formula in cell I$11$ to return the value in the $7$th column.