Excel VBA provides a function, but a formulaic solution also exists. The trick is to realize that:

- Dates are stored as numbers in Excel, and
- You can use the Cell() function to return the format of a cell.

- The cell contains a number, and
- Is formatted as a date.

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.