Excel assignment
The Leave.xls workbook tracks the amount of vacation time and family leave used by each employee in the Branco, Inc. Company. You will calculate how much vacation time and family leave each employee is eligible for, and then you can subtract from that amount the amount they’ve already used. You will also calculate the total number of vacation and family leave days used by all employees, as well as the total number of days remaining. Here are the eligibility requirements for the different vacation and family leave plans:

For vacation:
1. 17 days for full-time employees who have worked more than 5 years
2. 12 days for full-time employees who have worked more than 1 year
3. 7 days for full-time employees who have worked 1 year or less
4. 0 days for everyone else
For family leave:
1. 5 days for full-time employees who have worked 1 or more years
2. 3 days for full-time employees who have worked less than 1 year or for par-time employees who have worked more than 2 years.
3. 0 days for everyone else.

You’ll use these eligibility requirements to calculate the available vacation time and family leave for each employee. To complete this task:
1. Open the Leave1.xls file.
2. Enter the date and your name on the Documentation sheet and switch to the Employee Data worksheet.
3. In column D, enter a formula using a nested IF function to determine the number of vacation days each employee is eligible for based on the employee’s job status in column B and on the number of years employed in column C.
4. Subtract the amount of vacation time used from the available vacation time, displaying the remaining time in column F for all employees.
5. In column G, enter a formula using a nested IF function to determine each employee’s total family leave time. (use And, OR functions)
6. To determine the remaining families leave time, subtract the used portion of family leave from the total family leave and display the results in column I.
7. Switch to the Leave Summary worksheet. User the COUNTIF function to calculate the total number of employees eligible for the different vacation plans. An employee who eligible for the 17-day vacation plan will have the value 17 in Column D of the Employee Data worksheet.
8. Enter a formula to calculate the total number of employees in cell C8 by summing up the employees on each vacation plan.
9. In the range D4:D7, use the SUMIF function to calculate the total number of vacation days for each vacation plan. 10.In the range E4;E7, use the DSum function to calculate the total number of vacation days remaining.
11.In cell D8, enter a formula to calculate the total vacation days, and then, in cell E8, enter a formula to calculate the total days remaining