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