Tip Printed from ExcelTip.com
Calculate a weeks start date using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions



With the custom function below you can calculate the start date for any given week: 
Function WeekStartDate(intWeek As Integer, intYear As Integer) As Date
Dim FromDate As Date, lngAdd As Long
    If intYear < 1 Then intYear = Year(Date) ' the current year
    FromDate = DateSerial(intYear, 1, 1)
    If Weekday(FromDate, vbMonday) > 4 Then lngAdd = 7
    WeekStartDate = FromDate + ((7 * intWeek) - 6) - _
        Weekday(FromDate, vbMonday) + lngAdd
End Function