none
cDate function Type mismatch error - help me understand RRS feed

  • Question

  • Hi,

    Consider the following subroutine:

    Sub djdjd()
    
    Dim sDate As String, _
        dDate As Date
        
    sDate = "20141111"
    dDate = CDate(sDate)
    
    
    
    End Sub

    dDate = CDate(sDate)

    returns type mismatch error. I have no clue why...

    A Macro I use is fed a date formatted as a string (see sDate). How do I convert it to a legitimate date?

    Thanks :-)


    • Edited by Bednar87 Saturday, October 11, 2014 1:01 PM
    Saturday, October 11, 2014 1:00 PM

Answers

  • VBA doesn't recognize 20141111 as a valid date. It "sees" a huge number that does not fit in the date range that VBA supports. You can do the following:

        dDate = DateSerial(Left(sDate, 4), Mid(sDate, 3, 2), Right(sDate, 2))

    or

        dDate = CDate(Format(sDate, "@@@@/@@/@@"))


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

    • Marked as answer by Bednar87 Saturday, October 11, 2014 1:36 PM
    Saturday, October 11, 2014 1:33 PM
  • Re:  date returns type mismatch error. I have no clue why

    A date in Office is the number of consecutive days since the turn of the century (1900).
    Today (Oct 11, 2014) is 41923

    What you have is the number 20,141,111 converted to text.
    It is beyond the numeric limits established for a date.

    If you can parse the number into its components then the DateSerial function can handle it...
    dDate = DateSerial(year, month, day)  or  dDate = DateSerial(2014, 11, 11)

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Bednar87 Saturday, October 11, 2014 3:14 PM
    • Edited by James Cone Tuesday, October 25, 2016 3:10 AM update link
    Saturday, October 11, 2014 1:42 PM

All replies

  • VBA doesn't recognize 20141111 as a valid date. It "sees" a huge number that does not fit in the date range that VBA supports. You can do the following:

        dDate = DateSerial(Left(sDate, 4), Mid(sDate, 3, 2), Right(sDate, 2))

    or

        dDate = CDate(Format(sDate, "@@@@/@@/@@"))


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

    • Marked as answer by Bednar87 Saturday, October 11, 2014 1:36 PM
    Saturday, October 11, 2014 1:33 PM
  • Thanks. Used the first option.

    Cheers!

    Saturday, October 11, 2014 1:36 PM
  • Re:  date returns type mismatch error. I have no clue why

    A date in Office is the number of consecutive days since the turn of the century (1900).
    Today (Oct 11, 2014) is 41923

    What you have is the number 20,141,111 converted to text.
    It is beyond the numeric limits established for a date.

    If you can parse the number into its components then the DateSerial function can handle it...
    dDate = DateSerial(year, month, day)  or  dDate = DateSerial(2014, 11, 11)

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Bednar87 Saturday, October 11, 2014 3:14 PM
    • Edited by James Cone Tuesday, October 25, 2016 3:10 AM update link
    Saturday, October 11, 2014 1:42 PM