BYU Student Author: @Boston
Reviewers: @DylanKing, @Jake_Beckstead, @Christian, @Marco, @Alex_Garrett
Estimated Time to Solve: 15 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
You are working for a company and the CEO has decided to give a “loyalty incentive” to their employees. They decide to have two different rewards based on the amount of time an employee has been with the company. The CEO wants to see employees between five and nine years and those who have been there ten years and beyond.
Assume the date is 1/1/2023 for this challenge.
Instructions
The CEO has tasked you with finding these employees. They would like to see a report of all employees that fall in the first cutoff of five years and the second cutoff of ten years plus. You should display the following headers in your solution: FullName, HireDate with the full name displayed like “John Smith” and sort both lists by HireDate descending.
Data Files
Suggestions and Hints
Make sure to have the HireDate as a date and not a string or number! Text-to-columns could be helpful to parse the names. IF statements could be helpful to filter dates.
Solution
I didn’t separate my answers into different lists, I figure that could be done very easily with all the calculations done. Instead, I tried to write a single formula in each cell to reformat the employee name and then to also compute the cutoff for bonuses. Here’s my image with the formulas listed at the top.
6 Likes
I used another method. I wrote a single formula to reformat the names and used a function to find the years in service. Then, I used the filter tool. Might be cheating because I didn’t use IFs to filter that but it gets the job done.
5 Likes
I first split the names up and recombined them to be First Name Last Name, then copy-pasted the associated dates beside them. Then, I sorted the names by date, and copied unto another sheet everyone hired more than 5 years ago (HireDate > 1/1/2018). From that list, I found the cutoff date of ten years ago (1/1/2013), copied over all the names associated with that date and eariler, and placed it into a separate column for those who were hired 10+ years ago, beside the column of those hired 5-9 years ago.
Challenge8_Employee_Data.csv (72.1 KB)
3 Likes
I started by separating the column for names into 2 separate columns. Then I used a formula to combine the names again. I removed all the extra information and sorted it by date. I then removed all the extra information and split the 5-9 years column from the 10+ column and I was done.
Challenge8_Employee_Data.csv (64.8 KB)
2 Likes
I used a formula to pull all of the names in the correct order with their respective hire dates, and then used a UNIQUE function to pull all of the names onto the correct sheet. I had to then hardcode the names to their own list in order to sort the list.
Challenge8_Employee_Data.csv (281.0 KB)
2 Likes
EmployeeChallenge.xlsx (833.5 KB)
Here’s my solution!
I put the report on a separate worksheet which references the original data. The first formula puts the names in the proper format, the second finds the employees hire date (using an XLOOKUP which references a column I made on the other worksheet that has the employee names formatted in the same way as the report), and the third column identifies whether the employee qualifies for the 5-9 year, 10 year, or no bonus.
4 Likes
I used two “If” statements to determine which employees fell under working 5+ years and which fell under working 10+ years. I then used a formula to switch the orientation of the name. After doing both of those steps, I created formulas to organize and associate the full names with the hire dates and placed them on two separate pages. Check out my answer for more information!
Challenge8_Employee_Data My Solution.xlsx (709.9 KB)
1 Like
I probably did this the most difficult way possible, but I really wanted to do it without any hints. I started by breaking the names up using an IF function to find all the ones in the right time frame, then I had it print the name if it came back true. Once I had that I used a VLOOKUP to print the hire dates next to the names. Then I used a similar formula to David_Wood to put the names in the right format. Here is what I got:
I used excel’s data separation tools to help separate the names on the first sheet. I then used an if function to determine the length of employment. After getting the data that I needed I transferred the information over to a second sheet and organized it so it was easier to read.
Challenge8_Employee_Data.csv (201.9 KB)
I used some formulas to filter the data and the names. Then, with the clean data, I used other formulas to see the years and categorized the data with the specific years.
I Used the yearFrac function to calculate how many years each employee worked for the company, and then used if statements to display whether they worked less than 5 years, 5-10 years, or over 10 years.
2 Likes
I split the names and then used the concat function to put that back together in the right order. Then I filtered the dates. Fun challenege. Thank you!
Challenge8_Employee_Data.xlsx (708.2 KB)
1 Like
0205_Moore_Challenge8.xlsx (338.3 KB)
I used text-to-columns and then concat to combine the names in a new cell with a space in between. I found that I also had to repaste the tables as values because you otherwise end up with parsed formulas. I hid some columns I used for formulas in the final tables for aesthetics. I used many formulas and steps, and I’m sure there is a better way to accomplish this.
I started out by trying to combine the functions into one. I was running into trouble so I ended up sorting the names by date and moved the names with the correct dates to their corisponding columns. I’m going to continue to work on this to come up with a better solution.
Challenge8_Employee_Data(RG_sol).csv (200.5 KB)
1 Like
Challenge8_Employee_Data.csv (435.6 KB)
Submission for Maren Bayles
This was fun. I solved it in two ways. I first used an if and statement to find the employees who have been working for 5-10 years and 10+years. For the employees who have been hired for 5-10 years I used a nested filter function and for 10+
years I used a regular filter function to find the names and an xlookup to find the corresponding hire dates.
1 Like
