





Excel

Check cell for a valid "Excel recognized" date


Ease of Use

Easy

Version tested with

2000, 2002, 2003

Submitted by:

Zack Barresse

Description:

This function will return a Boolean (True/False) logical return depending if the cell referenced holds a valid Excel recognized date.

Discussion:

Excel currently does not hold any spreadsheet function to test whether or not a date in a worksheet is a valid date or text or something else. This User Defined Function allows us to over come this shortfall  as this function is then available for use as either a VBA or spreadsheet function. This function gives us the power we have in VBA to use in a logical formula expression.
Please note:
Expressions that use other formats (such as yeardaymonth) are not recognized as dates by Excel. However, because of the design of the IsDate function, it returns True for any expression that contains a year, a month, and a day in any order. In some cases the VBA IsDate function may give skewed results; this is a known MS bug.

Code:

instructions for use

Function IsADate(cel) As Boolean
IsADate = IsDate(cel)
End Function

How to use:

 Copy code.
 From Excel, press Alt + F11
 Select desired file on left*
 Insert  Module
 Paste code on right
 Press Alt + Q
 Save progress
 Enter as a normal function, =IsADate(A1)
 *If no pane exists on left, press Ctrl + R

Test the code:

 Enter a date in cell A1 (e.g. "2/2/2002")
 Enter text in A2 that looks like a date (e.g. "31 day of January, 2002")
 In B1 type the following formula =IsADate(A1), or other cell reference.
 Copy the formula to B2.
 Formula 1 should return TRUE; Formula 2 should return FALSE.

Sample File:

isadateEx.zip 6.84KB

Approved by mdmackillop

This entry has been viewed 171 times.

