» 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

CATEGORY - Custom Functions , Printing in VBA VERSION - All Microsoft Excel Versions

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