|
|
|
|
|
|
|
|
|
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 year-day-month) 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 177 times.
|
|
|