The beginning or end of previous week

For reporting, many times you need to figure out what date last week began and ended. Use the code below to figure that out:

Dim vntBegin As variantDim vntEnd As variantConst constSunday As Integer = 1GetPriorWorkWeek BeginDayOfWeek:=constSunday, WeekBegin:=vntBegin, _    WeekEnd:=vntEndPrint "Begin of Week: " & vtnBegin & ", End of Week: " & vtnEnd

This is the complete code of the GetPriorWorkWeek routine. You can easily modify it to work with any date passed as an argument, not just the current system date:

' Determine the date of the beginning and end of a week based on today' and the day the week begins on'' Input        : BeginDayOfWeek: 1 = Sunday thru 7 = Saturday' Input/Output : WeekBegin: It sets this to the first day of prior work week'                WeekEnd: It sets this to the last day of the prior work weekSub GetPriorWorkWeek(ByVal BeginDayOfWeek As Integer, Optional ByVal WeekBegin _    As Variant, Optional ByVal WeekEnd As Variant)   Dim dteDate As Date   Dim blnFound As Boolean   If BeginDayOfWeek < 1 Or BeginDayOfWeek > 7 Then      Err.Raise Number:=1000, Source:="GetLastDayOfMonth", _          Description:="Invalid BeginDayOfWeek, must be between 1 and 7"   End If   'Subtract 7 days ago to arrive at beginning date   dteDate = DateAdd("d", -7, Now)   Do Until blnFound      'If on the beginning of the week, stop;      'otherwise go back until the first day of the week is found      If Weekday(dteDate) = BeginDayOfWeek Then         WeekBegin = dteDate         blnFound = True      Else         dteDate = DateAdd("d", -1, dteDate)      End If   Loop   WeekEnd = DateAdd("d", 6, WeekBegin)End Sub