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.
See Question&Answers more detail:os