I have a Google Sheets spreadsheet with a date column. A contributor to this spreadsheet keeps entering dates in invalid format. I would like to conditionally format the cells in this column to have a red background if the text cannot be interpreted to be a valid date.

How can I do this?


It is possible to do this by using a custom formula in the conditional formatting. Assuming that you are conditional formatting the cells A1:A, enter this conditional formula:

  • 1
    I believe you could further clarify on what you mean by "dates in invalid format". For example both texts like 05/12/2020 and 12/05/2020 can be read as dates. Still. Depending on the sheets locale, just one of them is correct. Jul 27 '20 at 14:27
  • @marikamitsos This is what I mean: is the date in a format that Google Sheets understands? If I format the cells so that the month name automatically gets printed like this: "7 Aug 2020", will that work, or will Google Sheets fail to understand a date like "01.01.2020"?
    – Flimm
    Jul 27 '20 at 17:11
  • Well... Have you tried it? What did you find? Please edit both your question and answer including clarifications and results. Not many users make it to the comments. Jul 27 '20 at 17: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.