none
Differentiating between dates and text in Excel RRS feed

  • Question

  • I am trying to write a function in Excel that treats dates differently than text in cells. I am wondering if there is a boolean statement that will return TRUE when applied to a cell with a date and FALSE when applied to text (or vice versa). I was trying to work with functions that can transform dates into numbers, such as the MONTH function, to write this boolean. For example, "=MONTH(A1)>0." Obviously, this statement returns TRUE when applied to a date, but when this statement is applied to text it returns a #VALUE! error rather than FALSE. This is preventing me from moving forward. Any suggestions?
    Thursday, May 4, 2017 6:03 PM

All replies

  • You could use this custom VBA function:

    Function IsADate(d) As Boolean
        If IsDate(d) Then
            IsADate = (d >= DateSerial(1900, 1, 1))
        End If
    End Function

    Use like this:

    =IsADate(A1)

    Remark: the function returns true for cells containing true dates and also for cells formatted as text that contain a date.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 4, 2017 7:08 PM
  • I would have VBA test to see if it can convert the text to a date. If so than output TRUE otherwise FALSE.

    Public Function isDate(strText as string) as Boolean

    Dim dtmDate as Date
    On Error Goto isnotdate

    dtmDate = CDate(strText)
    isDate = True
    on error goto 0

    Exit Function

    isnotdate:
    isDate = False
    on error goto 0

    End Function

    Use as follows:

    =isDate(A1)

    • Edited by ITLOUIS Thursday, May 4, 2017 7:25 PM
    Thursday, May 4, 2017 7:23 PM