Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
please god why aren't these predefined?
' Expects a datetimestr in the format "YYYYMMDD" with - or / or no separator
' Parses independently of local region, unlike VBA.DateTime.DateValue()
' Known-good on Excel for Mac Version 16.4
Function parseYYYYMMDD(dateTimeStr As String) As Date
Dim normalized As String
normalized = VBA.Strings.Trim(dateTimeStr)
normalized = VBA.Strings.Replace(dateTimeStr, "/", "")
normalized = VBA.Strings.Replace(normalized, "-", "")
Dim datePart As String
datePart = normalized
Dim day As Integer, month As Integer, year As Integer
year = VBA.Strings.Mid(datePart, 1, 4)
month = VBA.Strings.Mid(datePart, 5, 2)
day = VBA.Strings.Mid(datePart, 7, 2)
Dim parsed_date As Date
parsed_date = VBA.DateTime.DateSerial(year, month, day)
parseYYYYMMDD = parsed_date
End Function
' Expects a datetimestr in the format "DDMMYYYY" with - or / or no separator
' Parses independently of local region, unlike VBA.DateTime.DateValue()
' Known-good on Excel for Mac Version 16.4
Function parseMMDDYYYY(dateTimeStr As String) As Date
Dim normalized As String
normalized = VBA.Strings.Trim(dateTimeStr)
normalized = VBA.Strings.Replace(dateTimeStr, "/", "")
normalized = VBA.Strings.Replace(normalized, "-", "")
Dim datePart As String
datePart = normalized
Dim day As Integer, month As Integer, year As Integer
day = VBA.Strings.Mid(datePart, 3, 2)
month = VBA.Strings.Mid(datePart, 1, 2)
year = VBA.Strings.Mid(datePart, 5, 4)
Dim parsed_date As Date
parsed_date = VBA.DateTime.DateSerial(year, month, day)
parseMMDDYYYY = parsed_date
End Function
' Expects a datetimestr in the format "04-11-08" or "04/11/08"
' Parses independently of local region, unlike VBA.DateTime.DateValue()
' Known-good on Excel for Mac Version 16.4
Function parseMMDDYY(dateTimeStr As String) As Date
Dim normalized As String
normalized = VBA.Strings.Trim(dateTimeStr)
normalized = VBA.Strings.Replace(dateTimeStr, "/", "_")
normalized = VBA.Strings.Replace(normalized, "-", "_")
Dim datePart As String
datePart = normalized
Dim dateParts As Variant, day As Integer, month As Integer, year As Integer
dateParts = VBA.Strings.Split(datePart, "_")
month = dateParts(0)
day = dateParts(1)
year = dateParts(2)
Dim parsed_date As Date
parsed_date = VBA.DateTime.DateSerial(year, month, day)
parseMMDDYY = parsed_date
End Function
' Splits s on sep and returns ith part, trimmed
Function splitTake(s As String, sep As String, i As Integer) As String
Dim parts As Variant
parts = VBA.Strings.Split(VBA.Strings.Trim(s), sep)
splitTake = VBA.Strings.Trim(parts(i))
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment