I loved this challenge! I learned to separate columns, concatenate them onto a different sheet, then sort the names and date columns by hire date. The exercise refreshed my basic excel understanding in a practical way.
1 Like
Challenge8_Employee_Data.xlsx (508.6 KB)
This was a good refresher on “If” functions and then being able to filter results to create the reports. Worthwhile challenge!
Challenge8_Employee_Data.xlsx (337.7 KB)
Challenge8_Employee_Data.csv (240.4 KB)
Challenge8_Employee_Data.xlsx (490.2 KB)
I solved this a couple of different ways, some more right than others. I used an if formula to see if the hire date landed within the range of 5.0-9.999 years, then I had it print out the name in the following line. The only problem was that the name wasn’t formatted correctly. I thought I had done it wrong, so I started over again and wrote a formula that would correctly format the names together, but then I had another idea of how I could solve the question. I don’t know why, the first way had worked fine, but I wanted to use a xlookup because that was one of the tags for this challenge. So, I had the formula look to make sure that the hire date was between 5-9 years, and then it would xlookup both the hire date and the social security number (just in case there were people hired on the same day) and print the name that relates to both of those keys. Then I did the same thing for 10+ Years. A longer way to get to the same answer.
1 Like
Challenge8_Employee_Data_Luis_Gonzalez.xlsx (357.4 KB)
This was a fun challenge, I wanted to do it a little different and I found a easy way to do it using the YEAR and the NOW functions, since it was super easy to calculate tenure of employees using those two functions, I also decide to calculate their age and include it in my results. After calculating those I use the filtering tool to delete everyone below 5 years with the company and to separate into two tables people who was in the company for more than 10 years or more and people who was in the company for 5-9 years.
I don’t know that I did it in the most efficient way, but I made new columns representing years in integers and also organizing the names properly and then using IF statements and xlookup to make the new lists on a separate tab. I also filtered the original data to sort by hire date to make things easier.
Challenge8_Employee_Data.xlsx (531.7 KB)
1 Like
Challenge8_Employee_Data (version 2).xlsb.csv (91.5 KB)
I also could have done this more efficiently with an xlookup function, but i ended up sorting the 5 and 10+ employees by hire date and then just copying and pasting them into different excel tabs to differentiate.
For my solution I created a formula that would identify how long each employee has been at the company and then put each employee into groups based on either 5 years or 10 years. I then put them into separate pages.
FaithCall_Challenge8_Employee_Data.xlsx (312.2 KB)
1 Like
Challenge8_Employee_Data.csv (402.8 KB)
Challenge8_Employee_Data_JamesGerstner.xlsx (364.5 KB)
For this challenge, I created a table for all the employees. I then sorted by the hiring date, then filtered by the 5-10 yr range and the 10+ year range. With each of these filters, I copied the contents into a table for each of the bonus ranges.
Sean Alder answer.csv (81.6 KB)
First, I split it up by 10+ years and 5-9 years, then I combined these onto the main sheet.
1 Like
I solved this very different than the video, but got the same answer. I used IFS and AND functions to determine how long an employee had worked for the company. I think i made it more complex than it needed to be, but it worked.
I started by using the concat, textbefore and textafter functions to create the FullName column. Then, I copied over the hiredate from column D. Then, I calculated the years elapsed to use as a criteria for my conditional statement for loyalty bonuses. I did so using the datedif, today and rounddown functions to get it to the year. Then, for the loyalty bonus I used a nestedif statement- having another if function called in the [value_if_false] attribute twice. Once I got this data, I copied it over to two other tabs/ sheets and used the sort and filter functionality on the home page of Excel to filter and organize the data.
1 Like
Here is my Excel File
Challenge8_Employee_Data.xlsx (569.2 KB)
I didn’t think of using the YearFrac function. Great idea and awesome work!
I started with the solution from the video but returned all the values in one table. I first filtered out the employees with an IF statement against “New” and “Five” then used the filter option to filter out the new employees. With the remaining employees, I wrote the formula for 10+ and did my if statement to return years worked using the new formulas, since the data was already filtered.
Challenge8_Employee_Data…xlsx (588.6 KB)
Challenge8_Employee_Data.csv (150.5 KB)
I used Text to Columns to correctly print out the full names. I used the following IF statements to determine the time of employment:
=IF(E2>=DATE(2018,1,1), “Less than 5 years”, IF(E2>=DATE(2013,1,1), “5-9 Years”, “10+ Years”))
Challenge8_Employee_Data.csv (201.9 KB)
Great challenge! Thanks for helping me learn Excel better!