excel - VBA从一年中的第几周获取开始日期

Question

我想在知道年份和周数的情况下检索一周的第一天(一周从星期一开始)的日期。 (1 年 52 周)

Dim nYear As Integer, _
    nWeek, As Integer
    startDate As Date

    nYear = 2018
    nWeek= 1


'startDate = ?

预期结果: 01/01/2018
有没有一种简单而动态的方法呢？

谢谢

Answer 1

Dim nTrimester As Integer, _
    nYear As Integer, _
    nWeek As Integer, _
    wd As Integer, _
    startDate As Date, _
    inputDate As Date

nYear = 2019
nWeek = 1    

inputDate = DateSerial(nYear, 1, 1)
inputDate = DateAdd("ww", nWeek - 1, inputDate)
wd = Weekday(inputDate, vbMonday)   

startDate = DateAdd("d", 1 - wd, inputDate)

编辑:
上面的答案考虑了一年中第一周的第一周。根据 ISO 8601，一年中的第一周是一年中的第一个星期四，所以，它应该是

Dim nTrimester As Integer, _
    nYear As Integer, _
    nWeek As Integer, _
    wd As Integer, _
    startDate As Date, _
    inputDate As Date

nYear = 2021
nWeek = 1

inputDate = DateSerial(nYear, 1, 1)

If Weekday(inputDate, vbMonday) > 4 Then
    nWeek = nWeek + 1
End If
    
inputDate = DateAdd("ww", nWeek - 1, inputDate)
wd = Weekday(inputDate, vbMonday)

startDate = DateAdd("d", 1 - wd, inputDate)

两种方法的不同之处在于，第一天，2021年第一周的第一天是2020-12-28，第二天是2021-01-04