Google Sheets highlight row based on conditional formatting custom formula

0
votes

I am trying to highlight the entire row on Google Sheets if the current date, TODAY(), is greater than or equal to the date value in Column A, AND if the current date is less than the date value in Column A plus 7. It is important that I keep the date string in the format of YYYY-MM-DD.

I have tried applying the following formula to the range A2:A100, but it not quite doing the trick:

=AND(DATEVALUE(TODAY()) >= DATEVALUE(INDIRECT(ADDRESS(ROW(),COLUMN()))), DATEVALUE(INDIRECT(ADDRESS(ROW(),COLUMN()))) < DATEVALUE(TODAY() + 7))

Any suggestions with this custom formula would be extremely appreciated!

enter image description here

1
google-sheetsgoogle-sheets-formulaconditional-formatting

1 Answers

2
votes

Use formula in CF if you have true dates:

=($A2<=TODAY())*(($A2+7)>=TODAY())

or this if you have dates as text

=(DATEVALUE($A2)<=TODAY())*((DATEVALUE($A2)+7)>=TODAY())

enter image description here