Table of Contents
Google Sheets is a powerful spreadsheet tool that allows users to organize and analyze data. One useful feature of Google Sheets is the ability to find the closest date. This can be useful when comparing data or tracking progress over time. To find the closest date in Google Sheets, users can use the “MIN” or “MAX” function, which calculates the minimum or maximum value in a range of cells. By combining this function with the “ABS” function, which returns the absolute value of a number, users can easily find the closest date to a specified date. This feature can save time and effort in data analysis, making it a valuable tool for users of Google Sheets.
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:
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.