Have you ever wondered how to find the closest date to today in Google Sheets? Whether it’s the nearest future date or the nearest past date, we’ve got you covered! In this article, we’ll show you how to use a few simple formulas to achieve this in no time. So, let’s dive in!
Formulas to Find the Closest Date to Today in Google Sheets
Imagine today is August 10th, 2018. You have a list of dates in column A, and you want to find the closest past and future dates to today. Whether your dates are in chronological, sorted, or unsorted order, these formulas will work like magic!
Here are the formulas you need:
Find the Past Closest Date to Today
=ArrayFormula(TO_DATE(MAX(if(A2:A<today(),A2:A))))Using the IF logical test, this formula finds the dates that are less than today. The MAX function then returns the latest date from that output, giving you the closest past date to today.
Find the Future Closest Date to Today
=ArrayFormula(TO_DATE(MIN(if(A2:A>today(),A2:A))))In this formula, the IF function finds the dates that are greater than today. The MIN function then returns the earliest date from that output, giving you the closest future date to today.
“Why is the TO_DATE function used in these formulas?”, you may ask. The TO_DATE function formats a date value to a date format. Without it, you would see the date value as output instead of the date itself. So, either format the cell to display the date or use the TO_DATE function to bring back the date value.
Additional Tips: Closest Date to Today in Vlookup
Now that you know how to find the closest date to today in Google Sheets, let’s take it a step further and learn how to use it in Vlookup. By combining these formulas with Vlookup, you can retrieve specific information based on the closest date to today.
In the Vlookup formula, use one of the previous formulas (highlighted in red) as the search_key. For example:
VLOOKUP(TO_DATE(MIN(if(A2:A>today(),A2:A))), range, index, [is_sorted])In this example, the range is the data range A2:C8, and the index is the 3rd column. By using the formula to find the closest future date as the search_key, you can retrieve information from the corresponding cell(s) in the row.
Note: In the screenshot, the formula is slightly modified with the ArrayFormula moved to the beginning. However, both variations of the formula will work without any issues.
That’s all there is to it! Now you can confidently find the closest past or future date to today in Google Sheets, and even use it in Vlookup to retrieve the desired information. Happy spreadsheeting!
Crawlan.com – Your ultimate source for all things Google Sheets and more!
