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.

Excel Find records of Minimum Date or Maximum Date