VBA cDate not working on Mac excel 2011(14.7.1)

Question

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

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

The above snippet runs fine on windows but is giving me type mismatch error on MAC. Is there any turnaround to convert a valid string in a date.

Also, what line are you getting the error on and what is the exact error message? — Dai, Jan 19 '17 at 10:22
I'm suspecting Variant datatype failure, have you tried `Dim d as Date` ? — Han Soalone, Jan 23 '17 at 12:02
Yes, I have tried that as well but it still gives me the same error. Also I have noticed that none of the date functions are working on MAC excel — Rohit Saluja, Jan 23 '17 at 14:06
Does this fix help [link](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/vba-for-excel-2011-no-longer-handling-dates-since/3d04e3b6-3cbc-4a36-89e8-323de4f1c7d1) — Han Soalone, Jan 24 '17 at 08:18
Tried to get that same error on my Mac today, but no luck. This makes me suspect that it is very much certain setting combination specific problem/bug, something I can't figure where the problem could be. Tried using US settings and such but it works perfectly fine on my Mac... You could try and create a dummy user account and check if there is something in your user profile that makes it act weird but other than that I'm all out of ideas.. — Han Soalone, Jan 25 '17 at 20:32

score 2 · Answer 1 · answered Jan 25 '17 at 21:43

2

Your regional settings are the root cause.

Can you try modify them?

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

answered Jan 25 '17 at 21:43

Jeandey Boris

743
4
9

This should be done in MacOS Terminal, related to system settings. – Han Soalone Jan 26 '17 at 12:52

sebifeixler · Answer 2 · 2017-01-24T14:01:55.410

1

Your code did work on my local OS X machine with Excel. I have different region settings, though. I would suggest try using 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 could try "Jan/1/2016".

P.S.: You can see your date & time settings in OS X under System Preferences -> Language & Region -> Advanced -> Dates:

edited Jan 24 '17 at 14:01

answered Jan 24 '17 at 13:52

sebifeixler

21
4

Please refer to the link provided by Hank in comments above – Rohit Saluja Jan 24 '17 at 16:09
The problem is if CDate is used to covnert from a weird format, as per [this question](https://stackoverflow.com/questions/23990811/convert-mmm-dd-yyyy-hhmmss-am-in-excel).... – Gert van den Berg Mar 03 '17 at 10:36

score 1 · Answer 3 · answered Jan 26 '17 at 12:05

1

Many different answer, so I may as well throw this way of doing it it into the ring,

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

answered Jan 26 '17 at 12:05

KyloRen

2,691
5
29
59

score 0 · Answer 4 · edited May 23 '17 at 10:29

0

Maybe try removing the "/" before converting? I do not have a Mac to test:

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

Also, have you tried checking for missing references? Date for VBA not working in Excel 2011?

edited May 23 '17 at 10:29

Community

1
1

answered Jan 25 '17 at 05:22

Jennifer

27
4

score 0 · Answer 5 · answered Jan 26 '17 at 10:31

0

You could use the IsDate() function to check if it is a date before converting. Otherwise use a workaround:

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

answered Jan 26 '17 at 10:31

sebifeixler

21
4

So far the only answer providing a possible workaround, however I'm not sure does `IsDate(str)` prevent the str being validated as a date although assigning `d` might fail. Also if the date string is something funny like "1/32/2017" we get to the else part where bad things start to happen. – Han Soalone Jan 26 '17 at 13:04
I was going to do this with a left/mid/right for the day month year, your answer is better with the split function. In my view this could be improved by putting it into a: Function ConDate(InputDate as string) as date – Preston Jan 26 '17 at 17:39
@HanSoalone This code was more a proof of concept than a working solution, it should be refined if that's an acceptable solution. Tom, thanks, it seems like a good idea! What's the best practice here? Should I upvote your solution? – sebifeixler Jan 30 '17 at 15:02
@sebifeixler I understood that and it is a good concept, I just wanted to point out the problems this approach might bring. Likely the solution will work ok, if added some error checking etc. – Han Soalone Jan 31 '17 at 06:46
It is sure a nice workaround but just want to let you guys know one thing, DateSerial is not working on my Mac – Rohit Saluja Jan 31 '17 at 08:54

Preston · Answer 6 · 2017-01-26T17:50:54.967

I didn't post this originally, because I felt it didn't address the root of the problem, however, if you want a workaround, you can use the below function in a similar way as you would use a CDate, by calling ConDate("12/12/2016").

This is is the way I approached the problem:

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

Not wanting to plagerise sebifeixler's answer I kept my original left/mid/right to separate the day/month/year, but I feel it's much neater to use his split function to separate the date by "/". A combination of the two would be a good workaround.

VBA cDate not working on Mac excel 2011(14.7.1)

6 Answers6

Linked