Warning
這個功能的使用有問題。 某些曆年中的最後一個星期一可以被歸還為第53週,而實際上應該是第1週。 如需詳細資訊及因應措施,請參閱 Format 或 DatePart 函數可能會傳回一年中最後一個星期一的錯誤週數。
癥狀
當你使用 Format 或 DatePart 函式來決定以下語法日期的週數時:
Format(AnyDate, "ww", vbMonday, vbFirstFourDays)DatePart("ww", AnyDate, vbMonday, vbFirstFourDays)
某些曆年中的最後一個星期一會被回傳為第53週,實際上應該是第1週。
原因
根據 ISO 8601 標準,當決定日期的週數時,Oleaut32.dll 檔案的底層函式呼叫錯誤地回傳了第 53 週,而非第 1 週,代表某些年份的最後一個星期一。
Resolution
使用使用者定義函式,根據 ISO 8601 標準的規則傳回週數。 本文中包含了一個範例。
其他資訊
ISO 8601 標準在歐洲廣泛使用,內容包括:
ISO 8601 "Data elements and interchange formats - Information interchange - Representation of dates and times"
ISO 8601 : 1988 (E) paragraph 3.17:
"week, calendar: A seven day period within a calendar year, starting on a Monday and identified by its ordinal number within the year; the first calendar week of the year is the one that includes the first Thursday of that year. In the Gregorian calendar, this is equivalent to the week which includes 4 January."
可透過對日曆週套用以下規則來實作:
- 一年分為52或53個日曆週。
- 一個日曆週有七天。 星期一是第一天,星期日是第七天。
- 一年的第一個日曆週包含至少四天的那一週。
- 如果某一年不是在星期日結束,那麼它的1到3個最後幾天要麼屬於明年的第一個日曆週,要麼是明年的前1到3天,要麼是今年的最後一個曆週。
- 只有從星期四開始或在星期四結束的一年,才有 53 個曆週。
在 Visual Basic 和 Visual Basic for Applications 中,除了 DateSerial 函式外,所有日期功能都來自對 Oleaut32.dll 檔案的呼叫。 由於 Format() 和 DatePart() 函式都能回傳指定日期的日曆週數,因此兩者都受到此錯誤影響。 為了避免這個問題,你必須使用本文提供的替代程式碼。
重現行為的步驟
在 Office 應用程式中開啟 Visual Basic 專案(Alt + F11)。
從 Project 選單中新增一個模組。
在模組中貼入下列程式碼:
Option Explicit Public Function Test1() ' This code tests a "problem" date and the days around it Dim DateValue As Date Dim i As Integer Debug.Print " Format function:" DateValue = #12/27/2003# For i = 1 To 4 ' examine the last 4 days of the year DateValue = DateAdd("d", 1, DateValue) Debug.Print "Date: " & DateValue & " Day: " & _ Format(DateValue, "ddd") & " Week: " & _ Format(DateValue, "ww", vbMonday, vbFirstFourDays) Next i End Function Public Function Test2() ' This code lists all "Problem" dates within a specified range Dim MyDate As Date Dim Years As Long Dim days As Long Dim woy1 As Long Dim woy2 As Long Dim ToPrint As String For Years = 1850 To 2050 For days = 0 To 3 MyDate = DateSerial(Years, 12, 28 + days) woy1 = Format(MyDate, "ww", vbMonday, vbFirstFourDays) woy2 = Format(MyDate, "ww", vbMonday, vbFirstFourDays) If woy2 > 52 Then If Format(MyDate + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then _ woy2 = 1 End If If woy1 <> woy2 Then ToPrint = MyDate & String(13 - Len(CStr(MyDate)), " ") ToPrint = ToPrint & Format(MyDate, "dddd") & _ String(10 - Len(Format(MyDate, "dddd")), " ") ToPrint = ToPrint & woy1 & String(5 - Len(CStr(woy1)), " ") ToPrint = ToPrint & woy2 Debug.Print ToPrint End If Next days Next Years End Function如果視窗還沒開啟,請使用 (Ctrl + G) 開啟即時視窗。
在 [即時運算] 視窗中輸入 ?Test1,然後按 Enter 鍵,請注意 [即時運算] 視窗中的下列結果:
Format function: Date: 12/28/03 Day: Sun Week: 52 Date: 12/29/03 Day: Mon Week: 53 Date: 12/30/03 Day: Tue Week: 1 Date: 12/31/03 Day: Wed Week: 1此格式下,所有週均從星期一開始,因此2003年12月29日應視為第一週開始,而非第53週的一部分。
在即時視窗中輸入 ?Test2,然後按 Enter,即可查看指定範圍內會發生此問題的日期清單。 清單包含日期、週日(始終週一)、格式回傳的週#(53)以及應回傳的週號碼(1)。例如:
12/29/1851 Monday 53 1 12/31/1855 Monday 53 1 12/30/1867 Monday 53 1 12/29/1879 Monday 53 1 12/31/1883 Monday 53 1 12/30/1895 Monday 53 1 ...
解決方法
如果你使用 Format 或 DatePart 函式,需要檢查回傳值。 當值為 53 時,再執行一次檢查,必要時強制返回 1。 以下程式碼範例示範了一種做法:
Function WOY (MyDate As Date) As Integer ' Week Of Year
WOY = Format(MyDate, "ww", vbMonday, vbFirstFourDays)
If WOY > 52 Then
If Format(MyDate + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then WOY = 1
End If
End Function
你可以寫程式碼來實現上述 ISO 8601 規則,避免使用這些函式來決定週數。 以下範例展示了一個替換函數用來回傳週數。
逐步操作範例
在 Office 應用程式中開啟 Visual Basic 專案(Alt + F11)。
從 Project 選單中新增一個模組。
在模組中貼入下列程式碼:
Option Explicit Function WeekNumber(InDate As Date) As Integer Dim DayNo As Integer Dim StartDays As Integer Dim StopDays As Integer Dim StartDay As Integer Dim StopDay As Integer Dim VNumber As Integer Dim ThurFlag As Boolean DayNo = Days(InDate) StartDay = Weekday(DateSerial(Year(InDate), 1, 1)) - 1 StopDay = Weekday(DateSerial(Year(InDate), 12, 31)) - 1 ' Number of days belonging to first calendar week StartDays = 7 - (StartDay - 1) ' Number of days belonging to last calendar week StopDays = 7 - (StopDay - 1) ' Test to see if the year will have 53 weeks or not If StartDay = 4 Or StopDay = 4 Then ThurFlag = True Else ThurFlag = False VNumber = (DayNo - StartDays - 4) / 7 ' If first week has 4 or more days, it will be calendar week 1 ' If first week has less than 4 days, it will belong to last year's ' last calendar week If StartDays >= 4 Then WeekNumber = Fix(VNumber) + 2 Else WeekNumber = Fix(VNumber) + 1 End If ' Handle years whose last days will belong to coming year's first ' calendar week If WeekNumber > 52 And ThurFlag = False Then WeekNumber = 1 ' Handle years whose first days will belong to the last year's ' last calendar week If WeekNumber = 0 Then WeekNumber = WeekNumber(DateSerial(Year(InDate) - 1, 12, 31)) End If End Function Function Days(DayNo As Date) As Integer Days = DayNo - DateSerial(Year(DayNo), 1, 0) End Function Public Function Test3() Dim DateValue As Date, i As Integer Debug.Print " WeekNumber function:" DateValue = #12/27/2003# For i = 1 To 4 ' examine the last 4 days of the year DateValue = DateAdd("d", 1, DateValue) Debug.Print "Date: " & DateValue & " Day: " & _ Format(DateValue, "ddd") & " Week: " & WeekNumber(DateValue) Next i End Function如果視窗還沒開啟,請使用 (Ctrl + G) 開啟即時視窗。
在 [Immediate window] 中輸入 ?Test3 並按 Enter 鍵,請注意 [Immediate window] 中的下列結果:
WeekNumber function: Date: 12/28/03 Day: Sun Week: 52 Date: 12/29/03 Day: Mon Week: 1 Date: 12/30/03 Day: Tue Week: 1 Date: 12/31/03 Day: Wed Week: 1星期一被視為第一週,這也理應如此。