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:

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

Test the code:

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

Sample File:

isadateEx.zip 6.84KB 

Approved by mdmackillop


This entry has been viewed 176 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express