asked on

get week ending date from week

In the Access 2003 app that I developed the user does not want to summarize resullts by specific date but instead wants them summarized by week.

I wrote a function to return the week of a particular date for 'passedDate'

getWeekOfDate = DatePart("ww", passedDate, vbMOnday)

It works well and the results are summarized by week. Now the user has requested to see the week ending date of the respective week shown on the resultant report.

For example:

Total Recs Week Week Ending Date
312 1 01/03/2010
461 2 01/10/2010

etc.......

I don't know of any way to reverse the transition from just knowing a week and a year to a week ending date. Does anyone have ideas preferably in the form of a nifty little function that does that?

rajvja

use WeekDay() function to get the week number. Do simple math calculation by substract or add to get the week biginning date and week end date.

mlcktmguy

ASKER

I don't undertsand how this could get me the week ending date if all I know is the year and week, For example Week 10 of 2009. Please show how this can be accomplished.

Colosseo

How about this:

EndDate = DateAdd("d",7 - DatePart("w", passedDate, vbMonday),passedDate)

The week of the year is irrelevant for this

HTH
Scott

Gustav Brock

Here is a generic function for this.

/gustav

Public Function DateWeekLast( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = vbUseSystemDayOfWeek) _
  As Date

' Returns the last date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.
' 2003-05-01. System settings used as default.
    
  ' No special error handling.
  On Error Resume Next
  
  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbUseSystemDayOfWeek
  End Select
      
  DateWeekLast = DateAdd("d", vbSaturday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
    
End Function

Open in new window

GRayL

Don't forget, just as you did a GROUP BY on your function that produced the week number, you will have to include the EndDate from Scott in the GROUP BY.

mlcktmguy

ASKER

I am very confused, not explaining myself well or both. To be clear:
I do not have a date such as 01/03/2010 available to pass to this new function. All I have is Year (2009) and Week (32). I need some way of determining the week ending date of Week 32 in year 2009 with only that information available. It would have to work like this:

dim passedWeek as long
dim passedYear as long

passedWeek=32 This would be dynamically loaded, not hard coded in the real code
passedYear=2009 This would be dynamically loaded, not hard coded in the real code

Dim returnWeekEndingDate as date

returnweekendingdate = GetWeekEndingDate (passedWeek, passedYear)

returnweekendingdate would now have a date which was the last day of week 32 in year 2009

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Sorry, got derailed by the other comments ...

It is not that simple to calculate but here is how.

/gustav

Public Function ISO_DateOfWeek( _
  ByVal intYear As Integer, _
  ByVal bytWeek As Byte, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

  ' The fourth of January is always included in
  ' the first week of year intYear.
  Const cbytDayOfFirstWeek  As Byte = 4
  ' Number of days in a week.
  Const cbytDaysOfWeek      As Byte = 7
  ' Month of January.
  Const cbytJanuary         As Byte = 1
  
  Dim datDateOfFirstWeek    As Date
  Dim intISOMonday          As Integer
  Dim intISOWeekday         As Integer
  Dim intWeekdayOffset      As Integer
  
  ' No specific error handling.
  On Error Resume Next
    
  If intYear > 0 Then
    ' Weekday of Monday.
    intISOMonday = WeekDay(vbMonday, vbMonday)
    ' Date of fourth of January in year intYear.
    datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
    ' Weekday of fourth of January in year intYear.
    intISOWeekday = WeekDay(datDateOfFirstWeek, vbMonday)
    ' Calculate offset from Monday in first week of year intYear.
    intWeekdayOffset = intISOMonday - intISOWeekday
    
    ' Weekday of requested weekday.
    intISOWeekday = WeekDay(bytWeekday, vbMonday)
    ' Calculate offset from requested weekday in first week of year intYear.
    intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
    ' Date of requested weekday in first week of year intYear.
    datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
  
    ' Date of requested weekday in requested week of year intYear.
    datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
  End If
  
  ISO_DateOfWeek = datDateOfFirstWeek
  
End Function

Open in new window

mlcktmguy

ASKER

Points are going to be awarded to GRayL. I ran 4 dates thru the proposed logic with the following resutls:

Date Grayl returned Cactus Data Returned
01/02/2003 which is week 1, 2003 1/5/2003 12/30/2002
07/13/1954 which is week 29, 1954 7/18/1954 07/19/1954
10/28/1990 which is week 43, 1990 10/28/1990 10/22/1990
10/28/2009 which is week 44, 2009 11/1/2009 10/26/2009

In 3 of the 4 cases it looks like Cactus Data is returning the week ending date of the prior week.

GRayL

Thanks, glad to help