Are you tired of manually searching for the current date in your Google Sheets? In this tutorial, we’ll show you a simple trick to create a hyperlink that will take you directly to the cell containing the current date. Say goodbye to scrolling through rows and columns just to find today’s date!
The Challenge with Manual Searching
Dates are a common element in any spreadsheet, whether they appear as headers or in transaction columns. When we need to find the current date, our first instinct might be to use the Ctrl+F shortcut for search. However, this method has a few downsides.
The search field requires us to input the date in the exact format used in the sheet. Forgetting the format or using the wrong one can lead to inaccurate search results. Plus, we might overlook the current date entirely, resulting in wasted time and frustration.
The Solution: Creating a Hyperlink Formula
To overcome these challenges, we can leverage Google Sheets’ Hyperlink function. This function allows us to create a hyperlink within a cell, making it easier to jump directly to the current date.
Let’s take a look at how we can create an internal hyperlink to the current date cell in Google Sheets.
Internal and External Hyperlinks
Before we dive into the specifics, let’s understand the difference between internal and external hyperlinks. An external hyperlink points to a webpage outside of your Google Sheets file, while an internal hyperlink refers to a location within the same sheet.
We want to create an internal hyperlink to the current date cell, so that the formula and the link reside in the same sheet.
Step-by-Step Guide
- Start by obtaining the URL of any cell in the current sheet.
- Remove the cell address from the end of the URL.
- Replace the
URL_of_the_sheetplaceholder in the generic formula with the URL obtained in step 2. The formula should look like this:=HYPERLINK("URL_of_the_sheet", "Jump to Today's Date"). - Append the following formula at the end of the URL:
&address(2, match(today(), B2:2, 0) + 1, 4).
The final formula should look similar to this:
=HYPERLINK("https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=352488775&range="&address(2, match(today(), B2:2, 0) + 1, 4), "Jump to Today's Date")
Let’s break down how this formula works.
The Address formula returns the cell ID of today’s date. In our example, the dates are in the range B2:G2. To make the range flexible, we use B2:2 instead.
The Match formula determines the relative column position of the current date within this range. By adding 1 to the Match output, we obtain the column number of the current date.
Finally, we combine the URL obtained in step 2 with the address of the current date cell, creating a dynamic hyperlink that jumps directly to today’s date.
Hyperlink in a Row
If your dates are organized in a row, you can follow the same steps outlined above. Simply replace A10 in the URL with a formula that returns the cell address of today’s date in that row.
Hyperlink in a Column
For dates arranged in a column, the process is similar. Just adjust the Match and Address formulas accordingly. Use the match formula match(today(), A2:A, 0) to find the relative row position of today’s date in the range A2:A. Add 1 to this result to get the row number.
In the Address formula, input 1 as the column number and the match formula as the row number.
Now, you have a hyperlink that takes you directly to today’s date, whether it’s in a row or a column.
Can We Use a Timestamp Column?
If you’re using a timestamp column and want to create a hyperlink to the current date, you’ll need to tweak the Match formula. Here are the modifications for both row-wise and column-wise timestamp columns:
In Row Wise:
match(today(), arrayformula(int(A2:A)), 0)
In Column Wise:
match(today(), arrayformula(int(B2:2)), 0)
And that’s it! With this trick, you can save time and effort by instantly jumping to the current date cell in Google Sheets.
Remember, exploring Google Sheets’ hidden features can significantly boost your productivity. Happy sheeting!
Sample_Sheet_231020
