(Solved) – Get a WeekNumber’s end date using excel macro


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
    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.

Leave a Reply

Your email address will not be published. Required fields are marked *