Highlight Entire Row When Date Column Is In The Same Month And Year As Today

How do you highlight an entire row in Google Sheets on a table of data where a column in your table containing dates matches the same month and year as today?

For this technique, you will need to use the Conditional Formatting section in Google Sheets and a Custom Formula.

Here’s the Custom Formula I needed to enter (I’ll explain it underneath so that you can amend it for your use case):


I’ve added line breaks to make it somewhat easier to digest, but here’s what’s going on:


As I want to compare each date to whether it’s the same MONTH and YEAR as today I need to use this function so that only rows that satisfy BOTH conditions are highlighted.


The MONTH function simply returns the month of the year of the date cell.


This is where you might need to modify what I have entered here. The A column contains the date values, if your dates are located in different columns, then change the "A" value to match your equivalent.


As previously mentioned this little snippet returns the current cell as Google Sheets iterates through each cell in the formatting range for this formula to be applied.


The comparison is then performed on the month of today.

This same code is then replicated for the YEAR value of the cells in the date column.

Here’s the finished product (those visiting in the future may need to scroll down to see the yellow highlighted row):

If you’d like your own copy to play around with, get a free copy by joining here:

[convertkit form=5673378]

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.