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:
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
