Objective: Learn how to use text and array formulas to locate potential payroll problems.
a. In column I, under the label “Ghost Employee?” write a function that compares the employee# in the timecards column to the employee# in the payroll master data column and displays the message: “Timecard employee# does not exist in master data” for any employee in the timecards columns who is not listed in the payroll master data columns. The function should leave the cell blank if the employee# in the timecards worksheet does exist in the payroll master file worksheet. (Hint: Use the ISNA and MATCH functions.)
b. In column L, titled “Invalid SSN?” write a function to identify invalid Social Security numbers. Assume that Social Security numbers that begin with the digit 9 or that have the digits 00 for the middle two numbers are invalid. Your function should display a message that flags either of these two conditions or which displays nothing otherwise. (Hint: there are text functions that examine specific portions of a string, such as the left 3 characters, and there are also functions that convert text to numeric values.)
c. In column P, titled “Missing Paycheck?” write a function to check whether a timecard exists for each employee in the master payroll data section of the worksheet. The formula should return either the message “No paycheck created for this employee” or display nothing.