vba - Get a WeekNumber's end date using excel macro

Question

I have 3 columns: Year, Weeknum, WeekRange. i'd like the WeekRange column to display the Start and End Dates based on the Year and WeekNum values. I found a code that calculates the Start Date and it works fine but i cant find anything that shows how to get the End Date.

here's the code i found(assuming the WeekNumber is 4 and the Year is 2020)

Function WeekStartDate(Optional intMonth As Integer = 1, _
Optional intDay As Integer = 1)

Dim FromDate As Date, lngAdd As Long
Dim WKDay, WDays As Integer

Dim intWeek, intYear As Integer
intWeek = 4
WDays = 0
intYear = 2020

'Calculating the date
FromDate = DateSerial(intYear, intMonth, intDay)


'Getting the week day # of the specified date considering monday as first day
WKDay = WeekDay(FromDate, vbMonday)

'If value of week day is greater than 4 then subtracting 1 from the week number
If WKDay > 4 Then
    WDays = (7 * intWeek) - WKDay + 1
Else
    WDays = (7 * (intWeek - 1)) - WKDay + 1
End If

'Return the first day of the week`enter code here`
WeekStartDate = FromDate + WDays

Appreciate any help i can get.

Answer 1

Here is a formula solution (no VBA required).

Assuming:

Cell A2 has the year.

Cell B2 has the week number.

Use this formula to get the WeekRange...

=TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,1) + 1,"mm/dd/yy") & " - " & TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,1) + 7,"mm/dd/yy")

The above also assumes you prefer the start of the week to be Sunday. If you would rather the start of the week be Monday then use this instead...

=TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,2) + 1,"mm/dd/yy") & " - " & TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,2) + 7,"mm/dd/yy")

Finally, you can change the format of the date by adjusting the occurrences of "mm/dd/yy" to suit your need.