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.
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:
Your regional settings are the root cause.
Can you try modify them?
defaults write NSGlobalDomain AppleICUDateFormatStrings -dict 1 dd/MM/yyyy
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?
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
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
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.