This tutorial explains how to find minimum date and maximum date of each record in Excel by sorting and remove duplicate.
You can find more tutorials at http://Access-Excel.tips
Find records of Minimum Date or Maximum Date, Why?
Find records of minimum date or maximum date is an essential skills in employee records. Some have data use an unique key such as employee ID plus a date as the unique record. For example, when Peter (Employee ID 001) was hired on 1/1/2014 and then terminated on 1/5/2014 (May 1st), you will find two records in the database.
| Employee ID | Name | Date | Position | Action |
| 001 | Peter | 1/1/2014 | Accountant | Hire |
| 001 | Peter | 1/5/2014 | Accountant | Termination |
| 002 | Mary | 1/1/2015 | Clerk | Hire |
| 002 | Mary | 1/2/2015 | Officer | Promotion |
| 003 | John | 6/3/2013 | Driver | Transfer |
When we need to run the latest status of Peter, we find the Termination record, the one of maximum effective date. If we want to run the first record of Peter, we need the record of minimum date.
How to find minimum date and maximum date of each employee
The best way to find minimum date and maximum date is to use SQL in Access. SQL allows you to use aggregate function “Max” and “Min”, and you can even select the Nth largest or smallest date.
Excel is not as effective as Access, but you can still get the job done. First, add Filter on the column header, then sort Date by descending order, sort Employee ID by descending order. We will have the maximum effective date on top of each Employee ID.
| Employee ID | Name | Date | Position | Action |
| 003 | John | 6/3/2013 | Driver | Transfer |
| 002 | Mary | 1/2/2015 | Officer | Promotion |
| 002 | Mary | 1/1/2015 | Clerk | Hire |
| 001 | Peter | 1/5/2014 | Accountant | Termination |
| 001 | Peter | 1/1/2014 | Accountant | Hire |
Now highlight the table, go to Data > Remove Duplicates
Check the “Employee ID” box, because we want to remove records where Employee ID are duplicated.
Since Excel will keep the first record and remove all records of the same Employee ID, you will get the maximum date as below.
| Employee ID | Name | Date | Position | Action |
| 003 | John | 6/3/2013 | Driver | Transfer |
| 002 | Mary | 1/2/2015 | Officer | Promotion |
| 001 | Peter | 1/5/2014 | Accountant | Termination |
To get the minimum date, sort Date by ascending order, sort Employee ID by descending order, then remove duplicates.
