Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB5,VB6
Expertise: Intermediate
Jul 22, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 variant
Dim vntEnd As variant
Const constSunday As Integer = 1
GetPriorWorkWeek BeginDayOfWeek:=constSunday, WeekBegin:=vntBegin, _
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
         dteDate = DateAdd("d", -1, dteDate)
      End If

   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.
Steve Miller
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date