Mehrab Imtiaz
Today we will see how to find and use the condition of if date is before today in Google Sheets.
Date calculations are quite common in Google Sheets, especially those that take advantage of comparisons.
So, we begin with a simple question.
How to Find if the Date is Before Today in Google Sheets?
We all know that Google Sheets stores date as integer values. We can easily prove that with the DATEVALUE function. The more recent the date, the more value it has.
What this means is that we can compare two dates and find which is older using simple mathematical comparators: Greater Than (>) or Less Than (<).
So, to find if a date is older than today or before today, we simply have to prove that today’s date is greater than the given date.
But before we get to the formula, let’s ask ourselves a simple question:
How do I get today’s date in Google Sheets?
While a no-brainer answer would be to type it down, Google Sheets allows us to generate today’s date automatically using the TODAY function.
This function is what we will be using in our formulas in this article.
So, without further ado…
Formula to Find if a Date is Before Today in Google Sheets
Here we have a simple dataset with a list of random dates. We will check whether these dates are older than today or not using the TODAY function.
Our formula:
=TODAY()>B3
Point to Note
The TODAY function is a “running” function. This means that anytime the spreadsheet is updated, the function recalculates to the most recent date.
If you want a non-updating static date for today, you can always type it down in a separate cell or use the keyboard shortcut CTRL+; (CMD+; for Mac) to generate today’s date. We can use a cell reference in our formula instead:
=$C$2>B5
Note: We have used absolute cell reference ($) to always point to a single cell for today’s date.
With this small improvement, you don’t have to worry about changing dates.
Learn more about TODAY and its uses: How to Use Today’s Date in Google Sheets (An Easy Guide)
Highlight if a Date is Before Today in Google Sheets Using Conditional Formatting
Instead of presenting our results in a separate column, it is more common to highlight cells with conditions like having a date before the current date in Google Sheets.
And the best way to highlight a cell with conditions is to use conditional formatting.
The Basic Option
We will work on the same worksheet. Navigate to Conditional Formatting from the Format tab.
Format > Conditional Formatting
In the Conditional formatting rules window, the first thing to do is set our format range. We have chosen the Date column.
Next, we set our conditions through Format rules. Conditional formatting has date-specific rules for us to use.
We will obviously be choosing Date is before and set it to Today.
Alternatively, we can also choose the following condition, Exact date, with the TODAY function to produce the same result with “running” dates:
The formatting can be updated according to the user’s requirements.
Practical Example: Highlight Another Cell if the Date is Before Today
In our last example, we highlighted dates that were before today in Google Sheets. But we highlighted the values of the same column.
In most practical scenarios, it is some other column that gets highlighted due to a condition in a column.
We have created one such example with the following dataset:
Here, we will look to highlight the Delivered column only if the date in the Date column is older than today.
So, back to the Conditional formatting rules window. This time, our format range will be the Delivered column.
This time, our change comes from the Format rules section. Instead of a date rule, we will choose the Custom formula is option.
Our custom formula is:
=TODAY()>B3
Note that this formula is the same that we discussed at the beginning of this article. Any Boolean outcome works perfectly with conditional formatting.
Click Done once you are satisfied with the formatting style to apply conditional formatting to cells if the date is before today in Google Sheets.
Final Words
That concludes all the ways we can use the condition of whether the date is before today in Google Sheets. This condition is primarily used to highlight and track dates. But with a little creativity, we can utilize the condition for other columns.
Feel free to leave any queries or advice you might have for us in the comments section below.
