VBA 将日期转换为周数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46973967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Convert date to week number
提问by USER7423
In VBA I want to convert a date as 03/11/2017(DD/MM/YYYY) into the week number for that date.
在 VBA 中，我想将日期 03/11/2017(DD/MM/YYYY) 转换为该日期的周数。
Until now I have the following code:
到目前为止，我有以下代码：
'geting the date out of the string
HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10)
'switch "." to "/"
HeadlineTemp = Replace(HeadlineTemp, ".", "/")
'convert to a date
FristVRFirstKW = CDate(HeadlineTemp)
Now, I need a function to convert that date into the week number of the year. First week day is Monday.
现在，我需要一个函数来将该日期转换为一年中的周数。第一个工作日是星期一。
回答by Vityata
To make the Week Number with Monday as a first day, use the following:
要将星期一作为第一天的周数，请使用以下内容：
WorksheetFunction.WeekNum(now, vbMonday)
回答by Ron Rosenfeld
Using VBA, to convert a date into an isoWeeknumber, you merely need the DatePartfunction (where DT is the date of interest):
使用 VBA，要将日期转换为 isoWeeknumber，您只需要该DatePart函数（其中 DT 是感兴趣的日期）：
isoWeekNumber = DatePart("ww", DT, vbMonday, vbFirstFourDays)
If you want to use other definitions than that specified in ISO 8601, investigate some of the other options for FirstDayOfWeekand FirstWeekOfYear
如果您想使用除 ISO 8601 中指定的定义之外的其他定义，请研究用于FirstDayOfWeek和FirstWeekOfYear
NOTE
笔记
As pointed out by @Mike85, there is a bug in DatePart(and also in the Format) function wherein Monday may be erroneously given a weeknumber of 53 when it should be 1.
正如@Mike85 所指出的，在DatePart（以及在Format）函数中存在一个错误，其中周一可能会错误地将周数设为 53，而应该是 1。
There are a variety of workarounds.
有多种解决方法。
In Excel 2013+ (Excel for Mac 2011+) you can use for the ISO Weeknumber:
在 Excel 2013+（Excel for Mac 2011+）中，您可以使用 ISO Weeknumber：
isoWeekNumber = WorksheetFunction.isoWeekNum(dt)
For earlier versions, you can test the Monday and adjust it if necessary, or you can write a separate routine.
对于较早的版本，您可以测试星期一并在必要时进行调整，或者您可以编写单独的例程。
回答by FunThomas
Be carefull when it comes to week numbers as there are different definitions around. The Excel definition differs from the ISO definition. To get the ISO weeknumber use (copied From http://www.rondebruin.nl/win/s8/win001.htm)
当涉及到周数时要小心，因为周围有不同的定义。Excel 定义不同于 ISO 定义。获取 ISO 周数使用（从http://www.rondebruin.nl/win/s8/win001.htm复制）
Public Function IsoWeekNumber(d As Date) As Integer
Dim d2 As Long
d2 = DateSerial(Year(d - Weekday(d - 1) + 4), 1, 3)
IsoWeekNumber = Int((d - d2 + Weekday(d2) + 5) / 7)
End Function
回答by mani
WeekdayName( number, [abbreviate], [firstdayofweek] )
WeekdayName(2)Result: 'Monday'
WeekdayName( number, [abbreviate], [firstdayofweek] )
WeekdayName(2)结果：'星期一'
WeekdayName(2, TRUE)Result: 'Mon'
WeekdayName(2, TRUE)结果：'星期一'
WeekdayName(2, TRUE, vbMonday)Result: 'Mon'
WeekdayName(2, TRUE, vbMonday)结果：'星期一'
回答by USER7423
So, this is my final and working perfectly version
所以，这是我的最终版本并且完美运行
Public Function IsoWeekNumber(d As Date) As String
Dim kwtemp As String
kwtemp = DatePart("ww", d, vbMonday, vbFirstFourDays)
If Len(kwtemp) = 1 Then kwtemp = "0" & kwtemp
IsoWeekNumber = kwtemp
End Function
If Application.International(xlMDY) = True Then
HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10)
HeadlineTemp = Replace(HeadlineTemp, ".", "/")
HeadlineTemp = Mid(HeadlineTemp, 4, 3) & Left(HeadlineTemp, 2) & Right(HeadlineTemp, 5)
VRFirstKW = CDate(HeadlineTemp)
HeadlineTempEndKW = Mid(VRHeadline, InStr(VRHeadline, "]") - 10, 10)
HeadlineTempEndKW = Replace(HeadlineTempEndKW, ".", "/")
HeadlineTempEndKW = Mid(HeadlineTempEndKW, 4, 3) & Left(HeadlineTempEndKW, 2) & Right(HeadlineTempEndKW, 5)
VREndKW = CDate(HeadlineTempEndKW)
VRKW = "KW" & IsoWeekNumber(VRFirstKW) & "-" & IsoWeekNumber(VREndKW) & "/" & Year(VREndKW)
Else 'don't switch position of the month with days
HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10)
HeadlineTemp = Replace(HeadlineTemp, ".", "/")
VRFirstKW = CDate(HeadlineTemp)
HeadlineTempEndKW = Mid(VRHeadline, InStr(VRHeadline, "]") - 10, 10)
HeadlineTempEndKW = Replace(HeadlineTempEndKW, ".", "/")
VREndKW = CDate(HeadlineTempEndKW)
VRKW = "KW" & IsoWeekNumber(VRFirstKW) & "-" & IsoWeekNumber(VREndKW) & "/" & Year(VREndKW)
回答by mike85
Calculate ISO year using datepart with bugs workaround:
使用 datepart 和错误解决方法计算 ISO 年份：
'Test 2007-12-31 should return W01Y2008
myDate = "2007-12-31"
ISOWeek = DatePart("ww", myDate, vbMonday, vbFirstFourDays)
Week1 = DatePart("ww", myDate, vbMonday, vbFirstFourDays)
Week2 = DatePart("ww", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays)
ISOYear = DatePart("yyyy", myDate, vbMonday, vbFirstFourDays)
Year1 = DatePart("yyyy", myDate, vbMonday, vbFirstFourDays)
Year2 = DatePart("yyyy", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays)
If ISOWeek = 53 And DatePart("ww", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays) = 2 Then
ISOWeek = 1
End If
if ISOWeek = 1 And DatePart("yyyy", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays) > ISOYear Then
ISOYear = ISOYear + 1
End If
MsgBox("W" & ISOWeek & "Y" & ISOYear)
' Result in W01Y2008
