VBA cDate not working on Mac excel 2011 (14.7.1)

Question

I am trying to convert a date to long using a VBA script. Below is a snippet of code

Sub test()
    Dim str as string
    Dim d as variant
    str="1/1/2016"
    d=cdate(str)
end sub

The above snippet works fine on windows, but gives me a type mismatch error on a MAC. Is there any twist to convert a valid string to a date.

Answer 1

Your code really worked on my local OS X machine with Excel. However, I have different region settings. I would suggest trying the international date format ("YYYY-mm-dd").

Sub Main
    Dim str as String
    Dim d as Variant
    str = "2016-01-01"
    d = CDate(str)
End Sub

Alternatively you can try "Jan / 1/2016".

---

P.S.: OS X → → → :

Answer 2

.

?

defaults write NSGlobalDomain AppleICUDateFormatStrings -dict 1 dd/MM/yyyy

Answer 3

, "/" ? Mac :

Public Sub test()
    Dim str As String
    Dim d As Long 'should be Date, but your question states Long
    str = "1/1/2016"
    d = CDate(Replace(str, "/", ""))
    Debug.Print d
End Sub

, ? VBA Excel 2011?

Answer 4

IsDate(), , . :

Sub test()
    Dim str as string
    Dim d as variant
    str = "1/1/2016"

    If IsDate(str) then
       d = CDate(str)
    Else
       Dim aDateParts() as String
       aDateParts() = Split(str, "/")

       ' DateSerial(Year, Month, Day)
       d = DateSerial(CInt(aDateParts(2)), CInt(aDateParts(0)), CInt(aDateParts(1)))
    End If
end sub

Answer 5

, ,

Sub test()
    Dim str As String
    Dim d As Variant
    str = "1/1/2016"
    d = CDate(Format(str, "dd/MM/yyyy"))
    Debug.Print d
End Sub

Answer 6

, , , , , , CDate, ConDate("12/12/2016").

:

Sub MainTest()
    Dim InputString As String, OutputDate As Date

    InputString = "01/12/2016"
    OutputDate = ConDate(InputString)

    Debug.Print OutputDate, TypeName(OutputDate)
End Sub

Function ConDate(ByRef InputString As String) As Date
    Dim Day As Long, Month As Long, year As Long
    'mmddyyyy format
    Month = CLng(Left(InputString, InStr(1, InputString, "/", vbTextCompare) - 1))
    Day = CLng(Mid(InputString, InStr(1, InputString, "/", vbTextCompare) + 1, InStrRev(InputString, "/", , vbTextCompare) - InStr(1, InputString, "/", vbTextCompare) - 1))
    year = CLng(Right(InputString, 4))
    ConDate = DateSerial(year, Month, Day)
End Function

sebifeixler, //, //, , , "/". .