How can you highlight a column of dates based upon a condition where comparison is needed with the current column of dates to another column containing dates?
I had a requirement where I needed to compare a column of dates to another column of dates and if a specific date was between those dates to highlight one of the columns.
For example, I needed to highlight a cell when the start date and the end date crossed over the end of the financial year. As the end of the financial year in Australia is 30th June each year if the start date was before the 30th June and the end date was after the 30th June then I wanted to highlight the row.
The formula was easy enough as other posts have demonstrated how you can use the Conditional Formatting feature in Google Sheets along with a Custom Formula to generate all types of useful highlighting functions.
Here was the final formula:
=AND(MONTH(INDIRECT("A"&ROW(INDIRECT("RC",FALSE))))<=6,
MONTH(INDIRECT("B"&ROW(INDIRECT("RC",FALSE))))>6)And here’s what each of the functions within this formula mean, starting with the inner most functions:
INDIRECT("RC", FALSE)
this function obtains the current cell when iterating through the Conditional Formatting range in the Google Sheet.
INDIRECT("RC", FALSE)
in a Custom Formula it will iterate over each cell in the range selected
ROW(INDIRECT("RC", FALSE))
the
ROW()
function obtains the row number of the current cell. For example, if the current cell were
A3
the row would be
3
, if it were
C45
it would return
45
.
INDIRECT("A"&ROW(INDIRECT("RC", FALSE)))
this creates a reference to the data contained in the
Start Date
column. For example, if the current cell was
C4
the row formula would
4
and this would create the range
"A4"
. With
INDIRECT()
wrapping around that reference Google Sheets would obtain the value in that cell, being
30/06/2023
.
MONTH(INDIRECT("A"&ROW(INDIRECT("RC", FALSE))))
returns the month of the date in the cell being referenced.
AND(
>6
MONTH(INDIRECT("A"&ROW(INDIRECT("RC", FALSE))))<=6
,
MONTH(INDIRECT("B"&ROW(INDIRECT("RC", FALSE))))
)
with the final function
AND
wrapping the check to see if both month values of the dates referenced have occurred either side of the month of June.
When pasting this formula into the
Value or formula
field, it will look like this:
If you see a red outline around the
Value or formula
field then it means there is something wrong with your formula. Check you’ve inserted the right number of brackets!
Once everything looks like it’s in working order click the Done button and you should see your rows highlighted according to your new conditional formatting.
Here’s how my sheet then looked:
Highlighting Based On Specific Date
While I made use of the
MONTH()
function to determine whether the two dates were on either side of a specific date, you could modify the formula to be more explicit on a date field.
To modify the custom formula to work around a specific date, use a cell and name it according to your needs.
In my case I have used a cell and named it
checkDate
:
Then in the conditional formatting custom formula for the entire range in the sheet enter the following formula:
Here’s the formula to insert:
=AND(INDIRECT("A"&ROW(INDIRECT("RC",FALSE)))<=INDIRECT("checkDate"), INDIRECT("B"&ROW(INDIRECT("RC",FALSE)))>INDIRECT("checkDate"))And the result is as follows:
Notice the slight change to the formula. Instead of using the
MONTH()
function the comparison is to a named cell and to use that named cell it needs to be wrapped with
INDIRECT()
.
Highlighting Rows Based On Date Cells In Row: Summary
To highlight an entire row based on the values of date cells in a common row use the
INDIRECT()
function along with the
AND()
function to create a formula that compares the cells containing your dates to the comparative date cell.
Other techniques you could use could be to compare the cells using date functions like the
MONTH()
function.
If you want to see more examples using the Conditional Formatting method with Custom Formulas check out this post .
If you wanted to check the Google Sheet out used in this post and make a copy for your own purposes, then you will find the Conditional Formatting Google Sheet here .
