How to Find the Closest Date in Google Sheets


You can use the following formula in Google Sheets to find the date in a column that is closest to a particular 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.

The following example shows how to use this formula in practice.

Example: How to Find Closest Date in Google Sheets

Suppose we have the following column of dates in Google Sheets:

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:

Google Sheets find closest date

The formula returns 8/1/2023, which is the closest date to 8/2/2023.

If we change the date in cell D1, the formula will automatically update to find the new closest date.

For example, suppose we change the date in cell D1 to 5/25/2023:

The formula now returns 5/22/2023, which is the closest date to 5/25/2023.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: Calculate Years Between Two Dates
Google Sheets: Apply Conditional Formatting to Overdue Dates
Google Sheets: How to Show Date as Month Name

One Reply to “How to Find the Closest Date in Google Sheets”

Leave a Reply

Your email address will not be published. Required fields are marked *