Calculate if Date is Before Today in Google Sheets

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.

date value to calculate if date is before today in google sheets

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.

dataset of dates

Our formula:


finding if date is before today in google sheets

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:


find if date is before today in google sheets with static date

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

navigating to conditional formatting

In the Conditional formatting rules window, the first thing to do is set our format range. We have chosen the Date column.

setting up the format range

Next, we set our conditions through Format rules. Conditional formatting has date-specific rules for us to use.

the date conditional format options

We will obviously be choosing Date is before and set it to Today.

highlighting if date is before today in google sheets with conditional formatting

Alternatively, we can also choose the following condition, Exact date, with the TODAY function to produce the same result with “running” dates:

alternative way to highlight date if before today in google sheets with conditional formatting

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:

a practical 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.

setting up conditional formatting range

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:


highlight cells if date is before today with conditional formatting

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.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply