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.