How to Find the Closest Date in Excel (With Examples)


You can use the following formulas in Excel to find the date in a column that is closest to a particular date:

Method 1: Find Overall Closest Date

=INDEX(A2:A15, MATCH(MIN(ABS(A2:A15-$D$1)), ABS(A2:A15-$D$1), 0))

This particular formula finds the date in the range A2:A15 that is closest to the date specified in cell D1, regardless of whether the closest date is before or after the specified date.

Method 2: Find Closest Date Before Specific Date

=MAX(($A$2:$A$15<$D$1)*A2:A15)

This particular formula finds the closest date in the range A2:A15 that is before the one specified in cell D1.

Method 3: Find Closest Date After Specific Date

=MIN(IF(A2:A15>$D$1,A2:A15))

This particular formula finds the closest date in the range A2:A15 that is after the one specified in cell D1.

The following examples show how to use each formula in practice with the following column of dates in Excel:

Example 1: Find Overall Closest Date 

We can type the following formula into cell D2 to find the date in the range A2:A15 that is closest to 8/2/2023:

=INDEX(A2:A15, MATCH(MIN(ABS(A2:A15-$D$1)), ABS(A2:A15-$D$1), 0))

The following screenshot shows how to use this formula in practice:

Excel find closest date

The formula returns 8/1/2023, which is the closest overall date to 8/2/2023.

Note: If cell D2 is shown as a numeric value then select cell D2 and then click the Number Format dropdown menu on the Home tab and then click Short Date:

The date will now be formatted as a date value.

Example 2: Find Closest Date Before Specific Date

We can type the following formula into cell D2 to find the closest date in the range A2:A15 that is before 8/2/2023:

=MAX(($A$2:$A$15<$D$1)*A2:A15)

The following screenshot shows how to use this formula in practice:

The formula returns 8/1/2023, which is the closest date before 8/2/2023.

Example 3: Find Closest Date After Specific Date

We can type the following formula into cell D2 to find the closest date in the range A2:A15 that is after 8/2/2023:

=MIN(IF(A2:A15>$D$1,A2:A15))

The following screenshot shows how to use this formula in practice:

The formula returns 8/5/2023, which is the closest date after 8/2/2023.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Find Earliest Date Based on Criteria
Excel: How to Check if Date is Between Two Dates
Excel: How to Use VLOOKUP by Date

2 Replies to “How to Find the Closest Date in Excel (With Examples)”

Leave a Reply

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