Excel: Check cell for date

Question

Short story short:

I want to check the cell C21 if it contains a date. I can't use VB cause it's deactivated by GPO.

Used this from this page

D21 contains this:

=WENN(ISTZAHL(DATWERT(C21));"date";"no date")
in english
=IF(ISNUMBER(DATEVALUE(C21))...

C21 this:

=HEUTE() # in english: =TODAY() Maybe other dates later, but allways in the correct format

but it allways returns "no date"

Answer 1

Use this formula, the expression will return TRUE if cell A1 contains an invalid date.

=ISERROR(DATE(DAY(A1),MONTH(A1),YEAR(A1)))

This formula works by evaluating each component part of the date: DAY, MONTH and YEAR and then aggregating them using the DATE function.

ISERROR will the catch any errors by returning TRUE (invalid) otherwise FALSE (valid).

Obviously the date value in cell (A1) must contain values >= 01/01/1900.

Useful for "Conditional Formatting".