excel - excel conditional formatting validate date

Question

I try to evaluate if a cell contains a valid date. If the content is not a valid date, the cell should be colored red. I want to do this without vb. On SO i got told, this is possible using only conditional formatting

Here is my try:

This doesn't work. I think the formular is fine, because i get no error message, but still no success.

---

Another try was to use Highlight Cells Rules and depending on a date occurring.

But here i can only use specific rules, like date is from this week, or this month, or past week. I can not say is a valid date or is a date between X and Y.

Any ideas how i can achieve my goal to set background-color of cells with invalid date-formats?

标签： excelconditional-formatting

Answer 1

问题在于 31.12.1899 不是 Excel 中的重新调整日期。
Excel 日期是一个数字，它以 0 = 开头00.01.1900

所以如果你31.12..1899用01.01.1900你的公式交换你的就行了。