Table of Contents
Finding the closest date in Excel can be achieved by using the “MIN” or “MAX” functions, along with the “ABS” function to calculate the absolute difference between the desired date and the dates in the data set. By inputting the desired date in a cell and using these functions, Excel will automatically return the closest date from the data set. This can be useful for tasks such as tracking deadlines or determining the next available date for an event. By utilizing these functions, users can easily and accurately find the closest date in Excel.
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:
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: