Need formula to choose between two dates

Hi,
I am trying to consolidate two reports into one. Each report is from a different company, but both companies employ many of the same people for both. I cannot upload the reports because they contain personal information, but here is what I am working with.....each report has columns for social security #, name, hours, earnings, birth-date, hire date, rehire date, and term date. The names don't always match exactly because of differences when they filled out their paperwork for each company, so I prefer to use the social security number as the comparing factor. My problem is that I need to consolidate the two reports, and I don't know how to get Excel to choose the right date when there are two different dates for a person. For example, say John Smith worked for Company A and Company B, but he was hired for Company A on 06/01/12 and Company B for 8/01/12. And say he was termed on Company A on 07/15/12 and termed on Company B on 12/01/12. I want to make sure that the formula chooses the oldest date (06/01/12) for his hire date and the newest date (12/01/12) for his term date. Is there a way to do this??? I have 340 employees between the two companies and it would be time consuming to have to do this manually.

It would be best to just give a sample, taking out confidential data, so that people could have a better understanding.

Question:
Is the rehire date, in your example, Aug 1, 2012?
How about the 2 term dates? Which header are they put on? I know one of them is the term date, but is the term date for the 1st term date (company A, July 15) or 2nd term date (Company B, Dec 1)?
Will it only have 2 companies max (company A and B) given the same social security #?

Here is the spreadsheet with sensitive data removed. As you can see, Company B has fewer employees so the data doesn't line up between the two. Example Worksheet.xlsx
The problem is that the companies still have other employees that didn't work on one or the other companies, so the columns between the two companies don't match. I need some kind of vlookup formula that can look at the two columns of social security numbers, and when they match, pick the oldest date (for the hire date) and the newest date (for the term date).

Thanks for the upload. Have looked at the file. Would you be able to give an example where an instance of a person who were hired in both companies? Change their SS# to say 100-001 etc

Also, from your attached, Row 29 of Company A, the re-hire date is earlier than the hire date. Is that even possible? If it is possible, would I be taking the re-hire date instead of the hire date as the 'oldest' date?

Last but not least, are you trying to compare 2 companies together?

1 easy way that I can think of is through pivot table, but that assumes the SS# is for the same person with the same birth date.

Okay, in trying to create an identifying factor (that didn't include sensitive data), I created an extra column in each company for a "new" ss#. I made the first "new" ss# in company A to be 000-00-0001 and then copied it down. I did a vlookup for company b to match the "new" ss# from company a to company b and then copied that down. Anything that ended with an n/a, I gave a ss# of 200-00-001 and then copied it down so I knew that those workers did not work on company A. Then I did the same with company A to find the workers that did not work on company B and those start with 100-00-0001. The two sets have been sorted so that the people that match are on top and those who don't are on the bottom. (a side note: I forgot to add back the company headers, but just know that the first set it company A and the second set is company B. Also, I removed the column that had the employee id because it's not needed. Example Worksheet 2.xlsx

The purpose for what I am doing is for a 401K census. I need to report earnings, hours, date of birth, hire date, rehire date, and term date for every employee that worked for all of our companies. We have 5 companies that I have to combine. All the other companies only have workers that worked on one company, so those are easy. But these two companies share workers.

BTW, the min/max formulas did not work with the dates.

Also, on line 29 for company A, the hire date is 06/01/09 and the rehire date is 03/10/12, so I think you are either looking at a different line or something because the hire date is before the rehire date. However, I am not comparing those columns against each other. I just need the earliest hire date in one column, the earliest rehire date in another column and the latest term date in another column.

Hi,

Attached is what I've come up the solution with.

Notice that I've combined the data from Sheet 2 (look at 'Solution' Sheet) and then create a pivot table (look at 'Pivot Solution' Sheet).

From the Pivot table, I can know that, there's a duplicate or not...notice that there's 2 person (based on B.O.D) that have the same SS# (row 86 highlight in yellow).

Thank you. I am unfamiliar with working with pivot tables, so it will take me awhile to figure out how you did all of that.
The highlighted person is actually the same person. The birthday is different because the person who filled out the birth-date on the I-9 wrote in the wrong date (they used the month of the hire date and the day and year of the birth-date). I have correct the employee master for that mistake and fixed the data on the spreadsheet for that.

Originally Posted by Melvinrobb

I am pretty sure the Max and Min formulas will work. If you put the SS# in T2, and the following formula in T3,

Formula:

Please Login or Register  to view this content.

you should have the oldest Hire Date.

A similar format would work to find the newest Term date, replacing Column 6 with Column 8, and MIN with MAX.

Thank you. That seems to be another way of doing it that would be easier for me to work with.