I used this formula after filtering for the first and last names:
=TRIM(MID(F3,FIND(“,”,F3)+1,LEN(F3))) & " " & LEFT(F3,FIND(“,”,F3)-1)
I went through and wrote a simple code that first determined the number of years that an employee had worked for the company according to the specified date. Then, I sorted from most time to least time worked. Then, using conditional formatting, I was able to highlight cells with numbers greater than 10 in green, while cells between 5 and 9 were highlighted in yellow further down the sheet.
I separated the list using the text to columns feature in excel. I then used the datedif function to calculate the difference in dates and then moved the relevant data to different sheets.
This took me about 25 minutes to complete. I first used a basic math function to find out the amount of time in years that each employee had worked at the firm. Then using “IF” and “AND” functions I did two separate functions to find the names of employees who had worked there for 10+ years and for 5-9 years. I then moved the two lists of names onto a separate worksheet. Then, I filtered out all the blanks from both lists, ensuring there were no duplicated names, and used a VLOOKUP to bring in the hire dates into the other worksheet. Next, using the TEXTBEFORE and TEXTAFTER functions as well as the & to join texts, I was able to switch the names from the “Last, First” format to the “First Last” format. I then hardcoded the information into the report worksheet so I could then filter on hire date by oldest to newest.
Overall, this challenge was a good exercise in using some excel functions that can often be used in various scenarios.
Challenge8_Employee_Data.xlsx (541.0 KB)
Time to Complete: 15 Minutes
Challenge8_Employee_Data.Nicole Santucci.csv (96.5 KB)