The beginning or end of previous week

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

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 Affiliate program also available at

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several