vba 将周数（和年份）转换为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42271678/
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 week number (and year) to date?
提问by user7415328
I have a week numberin Cell C13 and a yearin Cell C14.
我在单元格 C13 中有一个周数，在单元格 C14 中有一个年份。
I am using the below formula to convert this into the Thursday of that weeknumber's date:
我正在使用以下公式将其转换为该周数日期的星期四：
=DATE(C14,1,-6)-WEEKDAY(DATE(C14,1,3))+C13*7
How could i do the same thing using VBA?
我怎么能用 VBA 做同样的事情？
采纳答案by R3uK
For a specific year, you can do it like this :
对于特定年份，您可以这样做：
DateAdd("ww", WeekNumber - 1, DateSerial(2017, 1, 5))
And to test it :
并测试它：
Debug.Print Format(DateAdd("ww", WeekNumber - 1, DateSerial(YearNumber, 1, 5)), "ddd d MMM yy")
If others are looking into this and don't want a Thursday or don't work on 2017:
如果其他人正在研究这个并且不想要星期四或不在 2017 年工作：
change the 5 to fit your need!
Or use the below function
GetDayFromWeekNumber
更改 5 以满足您的需要！
或使用以下功能
GetDayFromWeekNumber
Code to test it :
测试它的代码：
Sub test()
Debug.Print Format(GetDayFromWeekNumber(2017, 1, 4), "ddd d MMM yyyy")
End Sub
And the generic function GetDayFromWeekNumber:
和通用功能GetDayFromWeekNumber：
Public Function GetDayFromWeekNumber(InYear As Integer, _
WeekNumber As Integer, _
Optional DayInWeek1Monday7Sunday As Integer = 1) As Date
Dim i As Integer: i = 1
If DayInWeek1Monday7Sunday < 1 Or DayInWeek1Monday7Sunday > 7 Then
MsgBox "Please input between 1 and 7 for the argument :" & vbCrLf & _
"DayInWeek1Monday7Sunday!", vbOKOnly + vbCritical
'Function will return 30/12/1899 if you don't use a good DayInWeek1Monday7Sunday
Exit Function
Else
End If
Do While Weekday(DateSerial(InYear, 1, i), vbMonday) <> DayInWeek1Monday7Sunday
i = i + 1
Loop
GetDayFromWeekNumber = DateAdd("ww", WeekNumber - 1, DateSerial(InYear, 1, i))
End Function
回答by Vityata
It works quite ok, taking in mind that the first day of the week is Sunday:
它工作得很好，请记住一周的第一天是星期日：
Function fnDateFromWeek(ByVal iYear As Integer, ByVal iWeek As Integer, ByVal iWeekDday As Integer)
fnDateFromWeek = DateSerial(iYear, 1, ((iWeek - 1) * 7) + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1)
End Function
You have to pass which day do you want as third parameter. Thursday is day number 5. Credits to these guys: http://www.dreamincode.net/forums/topic/111464-calculate-date-from-year-weeknr-and-daynumber/
你必须传递你想要哪一天作为第三个参数。星期四是第 5 天。感谢这些人：http: //www.dreamincode.net/forums/topic/111464-calculate-date-from-year-weeknr-and-daynumber/
回答by Ron Rosenfeld
As an additional option, for those using the ISO weeknumber system, where Week 1 of the year is the first week of the year containing four days (or the week that includes the first Thursday of the calendar year, and the first day of the week is Sunday.
作为一个附加选项，对于那些使用 ISO 周数系统的人，其中一年的第 1 周是一年中包含四天的第一周（或包括日历年的第一个星期四和一周的第一天的那一周）是星期天。
- DOW is an optional argument representing the day of the week. It will default to Thursday and, since we are using the ISO weeknumber system, should always fall within the current year.
- DOW 是表示星期几的可选参数。它将默认为星期四，并且由于我们使用的是 ISO 周数系统，因此应始终在当前年份内。
Option Explicit
Function WNtoDate(WN As Long, YR As Long, Optional DOW As Long = 5) As Date
'DOW: 1=SUN, 2=MON, etc
Dim DY1 As Date
Dim Wk1DT1 As Date
Dim I As Long
DY1 = DateSerial(YR, 1, 1)
'Use ISO weeknumber system
I = DatePart("ww", DY1, vbSunday, vbFirstFourDays)
'Sunday of Week 1
Wk1DT1 = DateAdd("d", -Weekday(DY1), DY1 + 1 + IIf(I > 1, 7, 0))
WNtoDate = DateAdd("ww", WN - 1, Wk1DT1) + DOW - 1
End Function
