# You manage a local bank in Dumas, Texas. A concern is retaining employees several years. You are…

You manage a local bank in Dumas, Texas. A concern is retaining employees several years. You are considering providing incentives to employees who have worked for the bank over five years. Your HR director provided a list of employees and dates hired in an XML document. You want to import the data into an Excel workbook, and then create a formula to calculate the number of years each employee has worked. In addition, you will insert some statistics to count the number of employees by years on the job and to apply a conditional format for employees who have worked over five years. This exercise follows the same set of skills as used in Hands-On Exercise 3 in the chapter. Refer to Figure 33 as you complete this exercise.

a. Use Windows Explorer to copy e09p3people.xml, and then rename the copied file e09p3people_ LastnameFirstname.xml.

b. Open e09p3employees in Excel, and then save it as e09p3employees_LastnameFirstname. Delete the data in the range C28:C30.

c. Import the XML data by doing the following:

• Click the Data tab, and then click From Other Sources in the Get External Data group.

• Select From XML Data Import, select e09p3people_LastnameFirstname.xml, and then click Open.

• Click OK in the Microsoft Excel message box.

• Click cell A9 to enter that cell reference in the XML table in existing worksheet box in the Import Data dialog box, and then click OK.

d. Click cell D9, type Years, and then press Enter.

e. Calculate and conditionally format the number of years by doing the following:

• Type =YEARFRAC(C10,B\$2) in cell D10, and then press Ctrl+Enter.

• Select the range D10:D21, and then apply the Comma Style.

• Click Conditional Formatting in the Styles group on the Home tab, point to Highlight Cells Rules, and then select Greater Than. Type 4.99 in the Greater Than dialog box, and then click OK.

f. Enter functions to count the number of employees by year by doing the following:

• Type the function =COUNTIF(D10:D21,”> =5″) in cell B7. Note: If you use the semi-selection method to select the range, the formula will look like this: =COUNTIF(Table1[Years],”>=5″).

g. Double-click between the column A and B column headings to increase the width of column A.

h. Save the workbook. Open e09p3people_LastnameFirstname.xml in Notepad. Scroll down to Drew Forgan’s information. Change the year from 2008 to 2003. Be careful not to delete any XML tags. Press Ctrl+S to save the XML document, and then close it.

i. Click the Data tab, and then click Refresh All in the Connections group. If warning messages appear, click OK in each message box, and then save the workbook. If the formula does not refresh, close the workbook, and then open it again. Adjust the width of column A, if necessary.

j. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of the worksheet.

k. Save and close the workbook, and submit based on your instructor’s directions.

Calculate the price
Pages (550 words)
\$0.00
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with MyhomeworkGeeks
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Testimonials
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
Psychology
quick, fast and exact!
Customer 453027, May 24th, 2022
Psychology
Customer 453027, January 15th, 2022
Nursing
Thank you so much for your help! :)
Customer 453167, May 11th, 2022
Other
Thank you , Excellent work
Customer 453201, June 2nd, 2022
Human Resources Management (HRM)
Always great! My son has been in critical health these last few semesters. Many times I have thought he wasn't going to make it and thought about quitting or postponing school until he was healthier but it always happens to be in the middle of the semester. This service has been a lifesaver for my mindset in helping keep my grades up and keep my son alive at the same time. I don't know if I could have made it through this period without it.
Customer 453077, June 6th, 2022
Social Sciences
Very fast report with excellent information on the topic. Thank you for your speedy service in such a short time.
Customer 453213, June 15th, 2022
English 101
IThank you
Customer 452631, April 6th, 2021
Human Resources Management (HRM)
My son and I have been sick for two weeks, of course at the end of my semester. I am so grateful that myhomeworkgeeks were able to help! Both papers got A's and I couldn't be more thrilled! Thank you so much and don't forget to tip your writer!
Customer 453077, February 22nd, 2022
Human Resources Management (HRM)
On time and as always a great service! This semester has been very time consuming a tough for my family so this service has not only been a life saver but helped me keep up my grades! If you are in a time crunch or need help, don't hesitate! They are worth it!
Customer 453077, April 4th, 2022
History
Looks great and appreciate the help.
Customer 452675, April 26th, 2021
Paper is great. Just only needed the one reference. Thank you
Customer 453139, May 4th, 2022
English 101
great summery in terms of the time given. it lacks a bit of clarity but otherwise perfect.
Customer 452747, June 9th, 2021
11,595
Customer reviews in total
96%
Current satisfaction rate
3 pages
Average paper length
37%
Customers referred by a friend