Table of Contents
Excel is a spreadsheet program that allows you to store, organize, and analyze data. The program has many features, but one of the most useful is the ability to find the closest date to a given date.
This can be useful when you want to find the most recent data for a given date range, or when you want to find the next date that falls on a given day of the week.
In this article, we’ll show you how to find the closest date to a given date in Excel. We’ll also provide some examples to show you how this function can be used.
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:
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.