Excel 2007 VBA CDate method accepted formats

Question

I am developing a change event in VBA for specific cells in Excel 2007. I want to convert dates (with time) with two different formats entered into these cells into one format (American).

Here is my code which checks if entered dates are in one of the two desired formats. crmdate is the String value of the ActiveCell:

    If RegexServiceManager.test(crmdate) Then
        outputDate = Format(CDate(crmdate), "MM/dd/yyyy hh:mm")
        Application.EnableEvents = False
        ActiveCell.Value = outputDate
        ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
        Application.EnableEvents = True
    ElseIf RegexSiebel.test(crmdate) Then
        outputDate = CDate(crmdate)
        Application.EnableEvents = False
        ActiveCell.Value = outputDate
        ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
        Application.EnableEvents = True
    Else
        MsgBox "Inapropriate date and time format"
    End If

RegexServiceManager checks if date is in YYYY/MM/DD HH:MM:SS format and this works fine. RegexSiebel checks if date is int DD-MMM-YYYY HH:MM format and this is where trouble begins.

I get a "Type mismatch" error on outputDate = CDate(crmdate) line. I have removed Format method like the one in the upper "If" to verfify that the error comes from CDate.

Could anyone advise on this? Maybe CDate does not recognize DD-MMM-YYYY (example: 01-Jan-2013) format? If so could anyone propose a workaround?

Target format is MM/DD/YYYY HH:MM.

Thank you & Best Regards,

Maciej

EDIT:

outputDate is of Date format!

CDate() does recognise the dd-mmm-yyyy hh:mm format. Try cdate(format(now(),"dd-mmm-yyyy hh:mm")) for example. — markblandford
Hello creamyegg, thank you for your tip. I think I found the naswer. I will post it as an answer. — Maciej Ogonowski

Maciej Ogonowski Maciej Ogonowski · Accepted Answer · 2013-04-05T09:36:38

I think I found the answer. It is a bit silly but the above code does not work with Polish regional settings. It works fine with American (and probably British too) regional settings.

I also changed the outputDate to Variant type.

I ended up with this:

    If RegexServiceManager.test(crmdate) Then
        MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
        outputDate = CDate(crmdate)
        MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
        Application.EnableEvents = False
        ActiveCell.Value = outputDate
        ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
        Application.EnableEvents = True
    ElseIf RegexSiebel.test(crmdate) Then
        MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
        outputDate = CDate(crmdate)
        MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
        Application.EnableEvents = False
        ActiveCell.Value = outputDate
        ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
        Application.EnableEvents = True
    Else
        MsgBox "Inapropriate date and time format"
    End If

The Message Boxes are just for debugging purposes.

It is probably wise to detect regional settings at the beginning of the program, or write it in a better way to avoid this. :)

Hope this helps someone.

Thank you & Best Regards,

Excel 2007 VBA CDate method accepted formats

1 Answers