google sheets: custom formatting basing on cell below

0
votes

I have column "start of the period" with dates like

2014-01-01
2014-01-07
2014-02-01
2024-02-12

and so on; I want cell with start of the current period to be highlighted, so I tried custom formatting formula

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

and it doesn't work. Is it even possible in new sheets without custom onEdit() script?

1
google-sheetsgs-conditional-formatting

1 Answers

1
votes

Yes it is possible. Use this in the custom formatting formula for the 1st cell

=and(today()>A1,today()<A2)

Google spreadsheets automatically changes the reference as you move down the row. So for the next row, the condition would become =and(today()>A2,today()<A3).

Interestingly, if you check the conditional formatting formula, it still displays the former formula, but works just fine. Check this sheet.