formula to check if it is a date

If your "date" in cell P1 is a text string and you want to check that it is valid, e.g.:

'30 June 2012     is a valid date

'31 June 2012     is NOT a valid date

you can use:

Try:

=NOT(ISERR(DATEVALUE(P1)))

DATEVALUE returns a date serial number from a text date.  If it can't it returns an error which is detected by ISERR.  The NOT function then reverses the result so your function returns TRUE if the string CAN be converted to a date.

This can all be shortened by using:

=ISNUMBER(DATEVALUE(P1))

Hope that helps.

Cheers
Rich 

Hi,

In Excel dates are numbers formatted to look like the dates we recognise. Today (12 June 2012) is 41072 or that many days since 1 jan 1900. we can ceck if the cell is formatted as a date but that doesn't mean it contains one or we can check if it's a number

=isnumber(p1)

But we can't specifically test if it's a date without resorting to VB code. The code below in a general module will test for a date. call with

=idate(P1)

Function idate(rng As Range) As Boolean
idate = IsDate(rng)
End Function