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):
=AND(
MONTH(INDIRECT("A"&ROW(INDIRECT("RC",FALSE))))=MONTH(TODAY())
,
YEAR(INDIRECT("A"&ROW(INDIRECT("RC",FALSE))))=YEAR(TODAY())
)I’ve added line breaks to make it somewhat easier to digest, but here’s what’s going on:
AND()
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.
MONTH()
The
MONTH
function simply returns the month of the year of the date cell.
INDIRECT("A"&ROW(...))
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.
INDIRECT("RC", FALSE)
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.
MONTH(TODAY())
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]
