Table of Contents
To find the closest date in Google Sheets, you can use the MIN function to determine the minimum value in a range of cells containing dates. This will return the cell with the smallest value, which is the closest date in the range. You can also use the MAX function to find the farthest date in a range of cells containing dates. This will return the cell with the highest value, which is the farthest date in the range.
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:
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.