How to apply conditional formatting in Google Sheets based on the next row value

Other Questions Google Sheets
1

I’m working with a date column that contains period start dates:

2014-03-15
2014-03-22
2014-04-01
2024-04-18

My goal is to highlight the row that represents the active period. I want the cell to be formatted when today’s date falls between the current row’s date and the date in the row below it.

I attempted to use this conditional formatting formula:

=AND(TODAY() >= R[0]C[0]; TODAY() < R[1]C[0])

However, this approach isn’t working as expected. The formatting doesn’t apply correctly and I’m not sure if I’m using the right syntax for referencing the cell below.

Is there a way to achieve this conditional formatting in Google Sheets without writing custom scripts? I’d prefer to use built-in formatting options rather than creating an onEdit() function if possible.

2

R1C1 notation doesn’t work for Google Sheets conditional formatting. You need A1 notation instead. If your dates are in column A starting from A1, use this formula: =AND(TODAY()>=A1,TODAY()<A2). Apply it to range A1:A4 and Sheets will auto-adjust the references for each row. The trick is applying the formatting to the whole range at once, not cell by cell. I’ve used this exact setup for billing periods and it works great. Just heads up - the last row won’t get formatting since there’s no row below it to compare against, but that’s usually what you want anyway.

3

OFFSET works fine, but I’d go with INDIRECT here. Try =AND(TODAY()>=A1,TODAY()<INDIRECT("A"&ROW()+1)) if your dates are in column A. This grabs the next row dynamically instead of hardcoding cells. I use this for project timelines and it handles adding/deleting rows way better than fixed references. Apply conditional formatting to your whole date range first, then drop this formula in the custom field. Just watch out - gaps in your date column will break it, so keep everything continuous.

4

yeah, r1c1 doesn’t work in google sheets like harry mentioned. try =AND(TODAY()>=A1,TODAY()<OFFSET(A1,1,0)) instead - offset grabs the cell below. works better than referencing A2 since it handles edge cases. just format your entire date range first, then add the formula.