Parse international standard date & time notation

This is a generic function for parsing international standard date & time notation. It should accept almost any valid ISO 8601 notation. The only exceptions to this rule are timezone (Z is not supported) and fractions of a second (ie. milliseconds are not supported).

More information about the international date & time notation

The following inputs are valid:

January 1st, 2010
2010-01-01
20100101

Day #1, 2010 (January 1st, 2010)
2010-001
2010001

December 31st, 2010
2010-12-31
20101231

Day #365, 2010 (December 31st, 2010)
2010-365
2010365

Week #1, 2010 (January 4th, 2010)
2010-W01
2010-W01-1 Monday
2010-W01-7 Sunday

All syntaxes also accept time (HH, HH:NN, and HH:NN:SS). Time must be separated from date by space or by letter T (as recommended by the standard). ":" characters are optional as "-" characters are optional for date separators.

Code:

'modInternationalDate.bas Option Explicit ' returns the date from a string formatted in ISO 8601 date & time notation Public Function IDate(Expression As String) As Date Dim DT() As String, lngLen As Long ' replace "T" -> " ", remove "-", ":" DT = Split(Replace(Replace(Replace(Expression, "-", vbNullString), ":", vbNullString), "T", " ")) lngLen = Len(DT(0)) ' date notation: by weeks or days? If InStr(DT(0), "W") = 5 Then ' remove W Mid$(DT(0), 5) = Mid$(DT(0), 6) lngLen = lngLen - 1 DT(0) = Left$(DT(0), lngLen) ' validate If IsNumeric(DT(0)) Then IDate = DateSerial(Left$(DT(0), 4), 1, -1) Select Case lngLen Case 7 ' 2010-W01-1 = by weeks + weekdays IDate = DateAdd("WW", Mid$(DT(0), 5, 2), IDate - Weekday(IDate, vbWednesday) - 2 + Right$(DT(0), 1)) Case 6 ' 2010-W01 = by weeks IDate = DateAdd("WW", Right$(DT(0), 2), IDate - Weekday(IDate, vbWednesday) - 1) Case Else ' error, but we have a year... IDate = IDate + 2 End Select End If ElseIf IsNumeric(DT(0)) Then Select Case lngLen Case 8 ' 2010-01-01 = by months + days IDate = DateSerial(Left$(DT(0), 4), Mid$(DT(0), 5, 2), Right$(DT(0), 2)) Case 7 ' 2010-001 = by days IDate = DateSerial(Left$(DT(0), 4), 1, Right$(DT(0), 3)) Case 6 ' 2010-01 = by months IDate = DateSerial(Left$(DT(0), 4), Right$(DT(0), 2), 1) Case 4 ' 2010 = by years IDate = DateSerial(DT(0), 1, 1) End Select End If ' time notation If UBound(DT) > 0 Then Select Case Len(DT(1)) Case 2 IDate = IDate + TimeSerial(DT(1), 0, 0) Case 4 IDate = IDate + TimeSerial(Left$(DT(1), 2), Right$(DT(1), 2), 0) Case 6 IDate = IDate + TimeSerial(Left$(DT(1), 2), Mid$(DT(1), 3, 2), Right$(DT(1), 2)) End Select End If End Function ' returns ISO 8601 date & time using week notation Public Function IWeek(Expression As Date, Optional ByVal IncludeTime As Boolean) As String Dim D As Date, Y As Integer, W As Byte, E As Byte E = Weekday(Expression, vbMonday) Y = Year(Expression) D = DateSerial(Y, 1, -1) D = D - Weekday(D, vbWednesday) - 2 + E W = DateDiff("WW", D, Expression) If W = 53 Then If E < 4 Then W = 1: Y = Y + 1 If Not IncludeTime Then IWeek = Right$("000" & Y, 4) & "-W" & Right$("0" & W, 2) & "-" & E Else IWeek = Right$("000" & Y, 4) & "-W" & Right$("0" & W, 2) & "-" & E & Format$(Expression, " HH\:NN\:SS") End If End Function

Sample:

Code:

Option Explicit Private Sub Form_Load() Dim intYear As Integer For intYear = 2000 To 2010 Debug.Print "Week #1 for year " & intYear, IDate(intYear & "-W01-1"), IIf(IDate(intYear & "-W53-1") = IDate((intYear + 1) & "-W01-1"), "52 weeks", "53 weeks") Next Debug.Print "Day 365 for year 2010", IDate("2010-365") Debug.Print "Dec 31th 2010, 23:59:59", IDate("2010-12-31 23:59:59") End Sub

Please about post any trouble or incorrect results you may have with the function so they can be fixed. Also remember to tell which locale you have troubles with.

Edit!
Further reading for me: http://en.wikipedia.org/wiki/Seven-d...Week_numbering