楼主
mxqchina |
网上搜集整理，放到一起，作为社区的一个资料贴，方便大家以后使用，非原创。
看到大家对2011-1-1属于2010年第52周，还是2011年第一周。有所疑问。其实根据ISO标准，已有定论。
根据ISO标准：第一周开始于星期一；或者包含星期四的第一周。公式1：- =1+INT((A2-DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,5)+WEEKDAY(DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,3)))/7)
公式2- =INT((A2-DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3)+WEEKDAY(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3))+5)/7)
VBA 方法1- Public Function IsoWeekNumber(InDate As Date) As Long
- IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
- End Function
可惜此方法有bug，具体见。如2009年12月29日，返回53，实际为第一周。
VBA 方法2- Public Function IsoWeekNumber2(d1 As Date) As Integer
- ' Attributed to Daniel Maher
- Dim d2 As Long
- d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
- IsoWeekNumber2 = Int((d1 - d2 + Weekday(d2) + 5) / 7)
- End Function
VBA 方法3- 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
VBA 方法4- 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当年的第一天：- =(YEAR(A2)&"-1-4")-MOD(YEAR(A2)&"-1-2",7)
只是搜集，其中含义不甚了解，大家一起讨论吧。
具体见附件。
首发excelhome，发现关注的人不多，放在这里。
 weeknum iso 20101012.zip
|