按ISO标准返回周（weeknum）6种方法 2010年10月12日更新

mxqchina

网上搜集整理，放到一起，作为论坛的一个资料贴，方便大家以后使用，非原创。
看到了这个帖子http://club.excelhome.net/thread-633453-1-1.html
其中对2011-1-1属于2010年第52周，还是2011年第一周。有所疑问。其实根据ISO标准，已有定论。

根据ISO标准：第一周开始于星期一；或者包含星期四的第一周。

1. ISO Week: The International Organization for Standardization (ISO) ISO8601:2000 Standard
   
2. All weeks begin on a Monday. Week one starts on Monday of the first week of the calendar
   
3. year with a Thursday.

复制代码

公式1：

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

1. =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

1. Public Function IsoWeekNumber(InDate As Date) As Long
   
2.     IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
   
3. End Function

复制代码

可惜此方法有bug，具体见。如2009年12月29日，返回53，实际为第一周。
http://support.microsoft.com/?kbid=200299

在 Visual Basic 和 Visual Basic 应用程序的除外 DateSerial 功能的所有日期功能都来自 Oleaut32.dll 文件的调用。因为 format () 和 DatePart() 函数可以返回给定日期的日历周编号，同时会影响此 bug。

VBA 方法2

1. Public Function IsoWeekNumber2(d1 As Date) As Integer
   
2. ' Attributed to Daniel Maher
   
3.     Dim d2 As Long
   
4.     d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
   
5.     IsoWeekNumber2 = Int((d1 - d2 + Weekday(d2) + 5) / 7)
   
6. End Function

复制代码

VBA 方法3

1. Function WeekNumber(InDate As Date) As Integer
   
2.   Dim DayNo As Integer
   
3.   Dim StartDays As Integer
   
4.   Dim StopDays As Integer
   
5.   Dim StartDay As Integer
   
6.   Dim StopDay As Integer
   
7.   Dim VNumber As Integer
   
8.   Dim ThurFlag As Boolean
   
9. 
   
10.   DayNo = Days(InDate)
    
11.   StartDay = Weekday(DateSerial(Year(InDate), 1, 1)) - 1
    
12.   StopDay = Weekday(DateSerial(Year(InDate), 12, 31)) - 1
    
13.   ' Number of days belonging to first calendar week
    
14.   StartDays = 7 - (StartDay - 1)
    
15.   ' Number of days belonging to last calendar week
    
16.   StopDays = 7 - (StopDay - 1)
    
17.   ' Test to see if the year will have 53 weeks or not
    
18.   If StartDay = 4 Or StopDay = 4 Then ThurFlag = True Else ThurFlag = False
    
19.   VNumber = (DayNo - StartDays - 4) / 7
    
20.   ' If first week has 4 or more days, it will be calendar week 1
    
21.   ' If first week has less than 4 days, it will belong to last year's
    
22.   ' last calendar week
    
23.   If StartDays >= 4 Then
    
24.      WeekNumber = Fix(VNumber) + 2
    
25.   Else
    
26.      WeekNumber = Fix(VNumber) + 1
    
27.   End If
    
28.   ' Handle years whose last days will belong to coming year's first
    
29.   ' calendar week
    
30.   If WeekNumber > 52 And ThurFlag = False Then WeekNumber = 1
    
31.   ' Handle years whose first days will belong to the last year's
    
32.   ' last calendar week
    
33.   If WeekNumber = 0 Then
    
34.      WeekNumber = WeekNumber(DateSerial(Year(InDate) - 1, 12, 31))
    
35.   End If
    
36. End Function
    
37. 
    
38. Function Days(DayNo As Date) As Integer
    
39.   Days = DayNo - DateSerial(Year(DayNo), 1, 0)
    
40. End Function

复制代码

VBA 方法4

1. Function WOY(MyDate As Date) As Integer    ' Week Of Year
   
2.   WOY = Format(MyDate, "ww", vbMonday, vbFirstFourDays)
   
3.   If WOY > 52 Then
   
4.     If Format(MyDate + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then WOY = 1
   
5.   End If
   
6. End Function

复制代码

增加根据年份返回ISO当年的第一天：

1. =(YEAR(A2)&"-1-4")-MOD(YEAR(A2)&"-1-2",7)

复制代码

只是搜集，其中含义不甚了解，大家一起讨论吧。
具体见附件。

[ 本帖最后由 mxqchina 于 2010-10-12 05:22 编辑 ]

ＺＨＹＺ

还是DatePart函数好,可选firstdayofweek 参数

mxqchina

更新：
根据年份返回ISO当年的第一天

mxqchina

原帖由 ＺＨＹＺ 于 2010-10-9 16:54 发表
还是DatePart函数好,可选firstdayofweek 参数

可惜有bug，比如2003年12月29日，因为第一周，而datepart返回53周。

madfrog

非常感谢LZ，总算找到符合欧洲标准的weeknum了，

penghu26

看不懂  呵呵   真难呀

lgcmeli

不错的文章。

mxqchina

现在看到EXCEL 2013中有个函数，测试后和上述公式1返回值完全相同。

ISOWEEKNUM 函数




本文介绍 Microsoft Excel 中 ISOWEEKNUM 函数的公式语法和用法。

说明

返回给定日期在全年中的 ISO 周数。

语法
ISOWEEKNUM(date)
ISOWEEKNUM 函数语法具有下列参数。
日期    必需。 日期是 Excel 用于日期和时间计算的日期-时间代码。

说明
Microsoft Excel 可将日期存储为可用于计算的序列数字。 默认情况下，1900 年 1 月 1 日的序列数字是 1，而 2008 年 1 月 1 日的序列数字是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。
如果日期参数为无效数值，则 ISOWEEKNUM 将返回错误值 #NUM! 。
如果日期参数为无效日期类型，则 ISOWEEKNUM 将返回错误值 #VALUE! 。

示例

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。 如果需要，可调整列宽以查看所有数据。




日期





2012-3-9





公式

说明

结果



=ISOWEEKNUM(A2)

2012-3-9 所在一年中的周数，一周开始于星期一（默认值）(10)。

10

walle666

学习了，谢谢

pxb_001

虽然不懂，但还是谢谢！