Format 或 DatePart 函數可能會對一年中最後一個星期一傳回錯誤的週數

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() 函式都能回傳指定日期的日曆週數,因此兩者都受到此錯誤影響。 為了避免這個問題,你必須使用本文提供的替代程式碼。

重現行為的步驟

  1. 在 Office 應用程式中開啟 Visual Basic 專案(Alt + F11)。

  2. 從 Project 選單中新增一個模組。

  3. 在模組中貼入下列程式碼:

    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
    
  4. 如果視窗還沒開啟,請使用 (Ctrl + G) 開啟即時視窗。

  5. 在 [即時運算] 視窗中輸入 ?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週的一部分。

  6. 在即時視窗中輸入 ?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 規則,避免使用這些函式來決定週數。 以下範例展示了一個替換函數用來回傳週數。

逐步操作範例

  1. 在 Office 應用程式中開啟 Visual Basic 專案(Alt + F11)。

  2. 從 Project 選單中新增一個模組。

  3. 在模組中貼入下列程式碼:

    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
    
  4. 如果視窗還沒開啟,請使用 (Ctrl + G) 開啟即時視窗。

  5. 在 [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
    

    星期一被視為第一週,這也理應如此。