Detecting Dates in Microsoft Excel Spreadsheets

Microsoft Excel does not have an "isdate()" function, making it hard to determine whether a cell contains a date.

Excel VBA provides a function, but a formulaic solution also exists.  The trick is to realize that:
  1. Dates are stored as numbers in Excel, and
  2. You can use the Cell() function to return the format of a cell.
Armed with these two pieces of information, we can write a formula that returns TRUE (or one) if:
  1. The cell contains a number, and
  2. Is formatted as a date.
Here's how.

Use the IsNumber() function to determine whether or not the value contained in a cell is a number, like this: =ISNUMBER(A1).

Use the Cell() function to get the format of a cell, like this: =CELL("format",A1).

If Cell() returns a result in the range "D1" to "D5", the target cell -- in this case A1 --  is formatted as a date.  Therefore, we can write: =OR(CELL("format",A6)={"D1","D2","D3","D4","D5"}).

Or, more compactly: =LEFT(CELL("format",A6),1)="D".

Now we just need to combine the two formulas with an And(), like this:
=IF(AND(ISNUMBER(A5),LEFT(CELL("format",A5),1)="D"),1,0).

This formula returns one if the cell is a number and it is formatted as a date, otherwise it returns a zero.
at