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
This tip has been brought to you by Pragmatic Software Co. Inc, the creators of Defect Tracker, the tool of choice for tracking functional specifications, test cases and software bugs. Learn more at http://www.DefectTracker.com. Affiliate program also available at http://www.PragmaticSW.com/AffiliateSignup.