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 W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

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

Leave a Reply

Your email address will not be published. Required fields are marked *