vba convert week number (and year) to date?

Question

I have a week number in Cell C13 and a year in Cell C14.

I am using the below formula to convert this into the Thursday of that week number's date:

=DATE(C14,1,-6)-WEEKDAY(DATE(C14,1,3))+C13*7

How could I do the same thing using VBA?

Answer 1

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 :

1. change the 5 to fit your need!

2. Or use the below function 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 :

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