1. On the Employee Data worksheet, create a function for the Work Anniversary column that will state Yes if it is the employees work month anniversary and will otherwise be blank. Use the Work Anniversary Month (cell B4) on the Data Tables worksheet. A formula that will pull the month from a date is =month( ).
2. Apply conditional formatting to the Work Anniversary column that will apply Green Fill with Dark Green Text for anyone who has a Work Anniversary.
3. In the Eligible for Bonus column, enter a formula to display the text No if the employee is not eligible for a Bonus and is blank if the employee is eligible for a bonus. Employees are not eligible for a bonus if they are part time or if they have a pay grade of D.
4. In the Comp Days column, enter a formula to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the Data Tables worksheet.
5. In the Bonus column, enter a function to calculate the bonus for each employee. The bonus information for qualifying employees is in a table named Bonus_Table in the Data Tables worksheet. Format with Accounting number, no decimal places.
6. On the Employee Analysis worksheet, in the Total Employees column, determine the number of full- and part-time employees.
7. On the Employee Analysis worksheet, in the Total Salary column, determine the total salaries of full- and part-time employees. Change the number to an appropriate format.
8. On the Employee Analysis worksheet, in the Average Salary column, determine the average salary of full- and part-time employees. Change the number to an appropriate format.
9. In cells B8 B10 enter the average salaries for individuals with the specified number of comp days who are full time employees.

11 Field 12 Employee ID 13 Last Name 14 First Name 15 Pay Grade 16 Hire Date 17 Years of Service 18 Birth Date 19 Age 20 Store 21 Job Status 22 Current Salary 23 Work Anniversary 24 Eligible for Bonus Data Definition Table - Employee Data Description Data Type Notes Employee ID Number 1000-9999 Employee last name Text Employee first name Text Employee pay grade for bonus Text A, B, C, or D Employee hire date Date Enter dates using mm/dd/yyyy Employee years of service Number One decimal place Employee birth date Date Enter dates using mm/dd/yyyy Employee age Number Integer Employee store Text Bonham, Bowie, Garland, or Graham Employee job status Text FT (Full Time) or PT (Part Time) Employee current salary Number Accounting format with two decimal places Employee work anniversary month Text Yes or blank Employee eligibility for bonus Text No' or blank Years of Service 1 and 5 and 10 receive 3 days Integer Comp Days 25 Employee bonus dollars Number Accounting format with two decimal places 26 Bonus 27 Documentation Ready IT Instructions Employee Analysis Employee Data Data Tables + A - - A Employee Analysis Total Salary Totad average salary Job Status Total Employees Average Salary FT 4. PT H Nm 70 000 Employees with Comp Days: 8 3 Comp Days 2 Comp Days 10 1 Comp Day 13 14 Instructions Employee Analysis Employee Data Data Tables + Documentation 17 Ready 12/31/17 Employee Dates 3 Years of service as of Work Anniversary this month 5 Age as of 1/1/17 Comp Days 8 1 and 5 and 10 Years of Service Nm Bonus 14 A 15 B 16 C 17 D 18. > Ready 800 600 300 100 Instructions Employee Analysis Employee Data Data Tables + Documentation J Last p a Grad Work Anniversary Ellsible for Bonus Comp Day: Bonus Years of Service 3.5 2.2 B 2.1 $ D 14.8 6.6 2.2 $ 5.7 6.5 9.7 stam Lori Dari Unda Richard Jessica Nick Melissa Brenda Susan Charles Joherry William Leroy Santos Rabert Ruth Marjorie Los Fua Shirley Robin Marvin Erin Earle Ramon Jason Catherine Alberta Kathy 1102 Delosreyes 1106 Goode 1110 Reams 1114 Rodriguez 1118 Peters 1122 Cartez 1126 Millard 1130 Burns 1134 Kimball 1138 Ford 1142 Vazquez 1146 Whetstone 1150 Arnold 1154 gasile 1158 Loftis 1162 Olson 1166 Gridley 1170 Esteve 1174 Carnan 1178 Ter 1182 Smith 1185 Lee 1190 Erwin 1194 Godrun 1199 Carnon 1202 Chavez 1206 Poulsen 1310 Lunsford 1314 Marciano 1318 Miller 1327 Wand 1325 Wilt 1330 Youre 1334 Maddox 1338 Escarcega 2.4 2.5 3.0 Hire Date 7/10/14 11/6/15 12/4/15 3/24/03 5/23/11 2/12/02 11/6/15 6/10/10 1/20/16 5/4/12 7/16/11 4/12/06 3/13/12 8/8/15 2/17/15 1/8/15 10/4/13 9/7/12 8/7/14 5/29/15 1/18/07 1/1/16 1/8/04 4/20/07 6/15/07 6/15/12 7/10/15 1/22/16 10/16/15 8/28/15 9/79/06 3/19/07 3/11/10 8/28/15 8/11/03 Birth Date 4/11/61 11/23/91 10/15/66 12/8/64 2/15/62 10/15/68 3/70/73 4/20/65 3/21/57 6/28/67 218/85 7/13/85 7/8/49 12/1/56 7/12/59 3/6/70 10/25/59 3/11/61 10/28/56 8/21/50 11/8/51 6/10/65 12/23/91 6/30/56 9/29/50 7/31/63 5/5/60 7/11/53 7/15/84 11/24/51 1/5/59 11/7/82 3/3/58 2/5/75 4/2/67 Ace Store 55 Bonham 25 Graham SO Bonham S2 Graham 54 Bonham 48 Bowie 43. Garland 50 Garland 59 Graham 49 Bonhamn 30 Graham 30 Garland 67 Bonham 60 Bonham S7 Garland 46 Bonham 57 Bowie 55 Garland 60 Graham 66 Graham 65 Garland 50 Graham 25 Graham 60 Graben 66 Graham 53 Gratin 56 Graham 63 Graham 32 Graham 65 Graham 57 Bowie 34 Graham 58 Garland 40 Bowie 49 Graham Job Status FT S FT S FT FT S FT S FT FT FT FT FT FT FT FT FT S FT FT S FT FT FT FT FT FT FT FT FT PT FT FT FT FT FT FT FT FT FT Current Salar 106,010 42.182 92,254 69.250 102,567 94,517 51,791 32,530 24,502 45,671 70.346 14,685 96.944 92.091 30,150 81.536 96,021 58,720 76,147 95,449 45,766 95,960 4,346 94,441 90.338 49.890 - HE - 11.0 2.0 - 14.0 10.7 10.6 55 2.5 2.2 2.3 11.3 10.8 Deam Thomas Alfonso Nicole Michael inanin in 52.244 92.221 35,304 51,675 90,283 45,657 39,545 125,C68 2.3 14.4 Documentation Instructions Employee Analysis Employee Data Data Tables + Ready 1 E - + 90% 11 Field 12 Employee ID 13 Last Name 14 First Name 15 Pay Grade 16 Hire Date 17 Years of Service 18 Birth Date 19 Age 20 Store 21 Job Status 22 Current Salary 23 Work Anniversary 24 Eligible for Bonus Data Definition Table - Employee Data Description Data Type Notes Employee ID Number 1000-9999 Employee last name Text Employee first name Text Employee pay grade for bonus Text A, B, C, or D Employee hire date Date Enter dates using mm/dd/yyyy Employee years of service Number One decimal place Employee birth date Date Enter dates using mm/dd/yyyy Employee age Number Integer Employee store Text Bonham, Bowie, Garland, or Graham Employee job status Text FT (Full Time) or PT (Part Time) Employee current salary Number Accounting format with two decimal places Employee work anniversary month Text Yes or blank Employee eligibility for bonus Text No' or blank Years of Service 1 and 5 and 10 receive 3 days Integer Comp Days 25 Employee bonus dollars Number Accounting format with two decimal places 26 Bonus 27 Documentation Ready IT Instructions Employee Analysis Employee Data Data Tables + A - - A Employee Analysis Total Salary Totad average salary Job Status Total Employees Average Salary FT 4. PT H Nm 70 000 Employees with Comp Days: 8 3 Comp Days 2 Comp Days 10 1 Comp Day 13 14 Instructions Employee Analysis Employee Data Data Tables + Documentation 17 Ready 12/31/17 Employee Dates 3 Years of service as of Work Anniversary this month 5 Age as of 1/1/17 Comp Days 8 1 and 5 and 10 Years of Service Nm Bonus 14 A 15 B 16 C 17 D 18. > Ready 800 600 300 100 Instructions Employee Analysis Employee Data Data Tables + Documentation J Last p a Grad Work Anniversary Ellsible for Bonus Comp Day: Bonus Years of Service 3.5 2.2 B 2.1 $ D 14.8 6.6 2.2 $ 5.7 6.5 9.7 stam Lori Dari Unda Richard Jessica Nick Melissa Brenda Susan Charles Joherry William Leroy Santos Rabert Ruth Marjorie Los Fua Shirley Robin Marvin Erin Earle Ramon Jason Catherine Alberta Kathy 1102 Delosreyes 1106 Goode 1110 Reams 1114 Rodriguez 1118 Peters 1122 Cartez 1126 Millard 1130 Burns 1134 Kimball 1138 Ford 1142 Vazquez 1146 Whetstone 1150 Arnold 1154 gasile 1158 Loftis 1162 Olson 1166 Gridley 1170 Esteve 1174 Carnan 1178 Ter 1182 Smith 1185 Lee 1190 Erwin 1194 Godrun 1199 Carnon 1202 Chavez 1206 Poulsen 1310 Lunsford 1314 Marciano 1318 Miller 1327 Wand 1325 Wilt 1330 Youre 1334 Maddox 1338 Escarcega 2.4 2.5 3.0 Hire Date 7/10/14 11/6/15 12/4/15 3/24/03 5/23/11 2/12/02 11/6/15 6/10/10 1/20/16 5/4/12 7/16/11 4/12/06 3/13/12 8/8/15 2/17/15 1/8/15 10/4/13 9/7/12 8/7/14 5/29/15 1/18/07 1/1/16 1/8/04 4/20/07 6/15/07 6/15/12 7/10/15 1/22/16 10/16/15 8/28/15 9/79/06 3/19/07 3/11/10 8/28/15 8/11/03 Birth Date 4/11/61 11/23/91 10/15/66 12/8/64 2/15/62 10/15/68 3/70/73 4/20/65 3/21/57 6/28/67 218/85 7/13/85 7/8/49 12/1/56 7/12/59 3/6/70 10/25/59 3/11/61 10/28/56 8/21/50 11/8/51 6/10/65 12/23/91 6/30/56 9/29/50 7/31/63 5/5/60 7/11/53 7/15/84 11/24/51 1/5/59 11/7/82 3/3/58 2/5/75 4/2/67 Ace Store 55 Bonham 25 Graham SO Bonham S2 Graham 54 Bonham 48 Bowie 43. Garland 50 Garland 59 Graham 49 Bonhamn 30 Graham 30 Garland 67 Bonham 60 Bonham S7 Garland 46 Bonham 57 Bowie 55 Garland 60 Graham 66 Graham 65 Garland 50 Graham 25 Graham 60 Graben 66 Graham 53 Gratin 56 Graham 63 Graham 32 Graham 65 Graham 57 Bowie 34 Graham 58 Garland 40 Bowie 49 Graham Job Status FT S FT S FT FT S FT S FT FT FT FT FT FT FT FT FT S FT FT S FT FT FT FT FT FT FT FT FT PT FT FT FT FT FT FT FT FT FT Current Salar 106,010 42.182 92,254 69.250 102,567 94,517 51,791 32,530 24,502 45,671 70.346 14,685 96.944 92.091 30,150 81.536 96,021 58,720 76,147 95,449 45,766 95,960 4,346 94,441 90.338 49.890 - HE - 11.0 2.0 - 14.0 10.7 10.6 55 2.5 2.2 2.3 11.3 10.8 Deam Thomas Alfonso Nicole Michael inanin in 52.244 92.221 35,304 51,675 90,283 45,657 39,545 125,C68 2.3 14.4 Documentation Instructions Employee Analysis Employee Data Data Tables + Ready 1 E - + 90%