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