-
May 20th, 2010, 04:36 PM
#1
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
Last edited by Merri; May 22nd, 2010 at 02:09 PM.
Reason: Bug fix: wrong variable in day calculation | IWeek change
-
May 21st, 2010, 01:23 AM
#2
Re: Parse international standard date & time notation
Just in case someone wonders why I don't use the DatePart function for week numbers, I noticed that it gives incorrect results.
Code:
Option Explicit
Private Sub Form_Load()
Dim intYear As Integer
For intYear = 2000 To 2010
Debug.Print DateSerial(intYear, 13, 0), IWeek(DateSerial(intYear, 13, 0)), DatePart("WW", DateSerial(intYear, 13, 0), vbMonday, vbFirstFourDays)
Next
End Sub
Code:
31.12.2000 2000-W52-7 52
31.12.2001 2002-W01-1 1
31.12.2002 2003-W01-2 1
31.12.2003 2004-W01-3 1
31.12.2004 2004-W53-5 53
31.12.2005 2005-W52-6 52
31.12.2006 2006-W52-7 52
31.12.2007 2008-W01-1 53
31.12.2008 2009-W01-3 1
31.12.2009 2009-W53-4 53
31.12.2010 2010-W52-5 52
So that is why I had to write a "cumbersome" week numbering math. But as far as I can see it works correctly
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|