Binaryworld - The beginning or end of previous week ... [ VB -> Date-Time ]

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

Click here to copy the following block

Dim vntBegin As variant
Dim vntEnd As variant
Const constSunday As Integer = 1
GetPriorWorkWeek BeginDayOfWeek:=constSunday, WeekBegin:=vntBegin, _
  WeekEnd:=vntEnd
Print "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 week

Sub 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