Valid Date ? (Excel 2003)

Topic

New Reply

Hi All …

Is there a formula/function that can easily tell if a date is valid? I have a cell (say A4) that uses VLOOKUP and can return 3 possibilities:
–a real date
–01/00/00
–#N/A

I would like to place a formula/function in a cell (say B4) that returns TRUE if this vlookup returns a real date.

Thanks,
–cat

Reply | Quote

Replies

What is the 01/00/00? If it is January 0, 1900 Excel still considers it to be a valid date.

There is no worksheet function that tests for a valid date. You could create a custom function:

Public Function IsADate(ByVal Arg) As Boolean
Application.Volatile
On Error GoTo ExitHere
IsADate = IsDate(Arg) And Arg > 0
ExitHere:
End Function

If you put it in a module in the workbook, you can use it like this:

=IsADate(A4)

Reply | Quote

01/00/00

is the result of a VLOOKUP that finds the row, but the column that it is pulling is blank but formatted as a date.

–cat

Reply | Quote

OK, the IsADate function from my previous reply should handle that.

Reply | Quote

Hans: Why does this function need to be Volatile?

Reply | Quote

It doesn’t, actually.
I have a tendency to add Application.Volatile to UDFs as a precaution…
Thanks!

Reply | Quote

That tends to create a LOT of overhead!

Reply | Quote