# Excel Find records of Minimum Date or Maximum Date

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.

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist