I'm trying to apply Conditional formatting applying to column K (range K2:K1001). What I want to do is to highlight each cell in the column if it's date value (e.g., "6/1/2016") is less than a date in each cell in K2:K10001 minus 7 days.

How do I make this happen across all cells in column K all at once without having to write a formula (or something equivalent) one cell at a time? Basically I want to work as I do in Excel - write one formula - then copy/paste down the entire column.

  • Have you tried a date a little closer to home. eg. 6/2/2020? It's just that 6/2/2100 is out-of-range for many date systems, so could be returning a value that does satisfy < (today() - 7) after all. However, this is a guess, google-sheets might have a better date implementation. – MrWhite Apr 24 '16 at 8:48
  • 4
    Impossible to answer without knowing what exactly your formula is. A link to shared spreadsheet where the problem occurs would also be helpful.' – user79865 Apr 24 '16 at 17:19
  • Formulas for conditional formatting could be checked as usual formulas, they behave the same. Enter in any cell ="6/2/2100"< today() -7 and the result is false. Need a sample file or more details to help you. I'll downvote the question till it's not clear how to solve it. – Max Makhrov Apr 25 '16 at 6:16
  • @Meta I prefer to ask anonymously without linking to a named sheet. Max Makhrov, I think this is much more clear. – Nona Apr 27 '16 at 5:37

Google Docs help gives good instruction on how to make Conditional formatting, for your case Custom formula is:

| improve this answer | |
  • thanks, my question is for each date in the cell in the range K2:K1001. Each cell in column K will have a date and I want to hightlight it according to the "today()-7" criteria. How do I do this? – Nona May 6 '16 at 6:28
  • select K2:K1001 → make Conditional formatting → your formula → =$k2<today()-7 – Max Makhrov May 6 '16 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.