Google Sheets Conditional Formatting Date

In this post we will look at how to use Google Sheets conditional formatting to change the colour of cells based on the given date. Here’s an example of what we will achieve:

The conditional formatting rules I have applied here are:

• Green if the date is within the last 7 days
• Yellow if the date is within the last 30 days
• Red if the date is older than 30 days

And it’s actually much easier than you might think (and easier than I personally expected).

Here’s the conditional formatting rule setup inside the Google Sheet:

Notice that my date values are in American format: YYYY/MM/DD

The date format you have doesn’t matter so long as all the dates follow the same format.

Here’s an example that used UK date format: DD-MM-YYYY as input, which Google Sheets automatically converts, replacing the dash with a slash (hey, that rhymes):

The colours of those cells are different just to highlight that you don’t need to use red / yellow / green.

Also in that example I used a date in the future, rather than the past:

OK, so the magic has kinda already been revealed here.

The trick is to use the TODAY() function, and Google Sheets will smartly do all the hard work for you behind the scenes.

Here’s a link to the official Google docs for the TODAY() function.

And here’s a screenshot of what that page looks like, because for me, that link was not always up – so this is just a quick screen grab for reference if the link breaks:

How To Apply Conditional Formatting For Dates In Google Sheets

I’m going to assume you have a Google Sheet already that has some dates for which you want to apply your conditional formatting.

If you do, you can skip this part – scroll down to the next subheading.

Setting Up Some Fake Data

But if you don’t, you can very quickly set up some fake date using a little shortcut.

So here’s the alphabet data I used:

aaa
bbb
ccc
ddd
eee
fff
ggg
hhh
iii
jjj
kkk
lll
mmm
nnn
ooo
ppp
qqq

rrr

Literally just copy / paste that into a Google Sheet and it should look like this:

OK, so far, so good.

Now the magic part – creating the dates.

In the next cell along – cell J in my case – type in today’s date:

You can then drag this down by clicking on the little blue square in the bottom right corner of the cell, and it will fill in all of the cells that you drag into:

Perhaps more fun for us, we can use the same approach but if we use two dates to begin with, with a gap in between of a day, Google Sheets (and Excel for that matter) is smart enough to figure out we want a gapped day range:

That is our starting point.

Applying Conditional Formatting To Date Cells

You can apply conditional formatting to an individual cell, a range of cells, or a full column.

Technically a full column is still a range of cells, but let’s not split hairs.

You can either:

• Right click a cell
• Right click the column heading (i.e. J in the clip above)
• Or select an individual cell, or range of cells, and go to Format > Conditional formatting

That should then pop up the Conditional format rules pane:

For some reason it comes with a default for me.

The default is not what I want, but it’s a starting point.

To quickly recap, what we want is:

So it’s really just a case of inputting them using the rules and styles.

Here’s what I put in:

• Format cells if: Greater than or equal to
• Using the formula: =TODAY()+30
• With a formatting style of light red

The trick is to tweak the formula as needed.

In this instance we have dates going away from today. So in that case we want to use TODAY() as our starting point. That would give 7th February 2023 as that’s the current date at the time of writing.

Then we add 30 days on to that date.

Because TODAY() is a function, it will update all the time. So if you were to load up the Google Sheet tomorrow, that TODAY() value would become 8th February 2023.

In order to get the other colours in, we need to add two more rules:

And then just repeat the process with the values:

• Format cells if: Greater than or equal to
• Using the formula: =TODAY()+7
• With a formatting style of light yellow

And finally:

• Format cells if: Less than
• Using the formula: =TODAY()+7
• With a formatting style of light green

Which should give:

At this point it’s a case of tweaking the formula as you need.

You can also drag and drop to re-arrange your conditional formatting rules, if needed. To do this, hover over the rule and it should give three little dots on the left side of the rule. Then click there and drag and drop to re-order. The rule order makes no difference in our case:

And there we go. A simple way to conditionally format date based cells in Google Sheets.