• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 916
  • Last Modified:

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


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?
  • 3
  • 3
  • 2
  • +2
1 Solution
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.

mlcktmguyAuthor Commented:
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.
How about this:

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

The week of the year is irrelevant for this


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Gustav BrockCIOCommented:
Here is a generic function for this.

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, _
    Case Else
      lngFirstDayOfWeek = vbUseSystemDayOfWeek
  End Select
  DateWeekLast = DateAdd("d", vbSaturday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
End Function

Open in new window

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.
mlcktmguyAuthor Commented:
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

Try this:

yr = 2010
wk = 3
? DateAdd("d",7 - DatePart("w", dateadd("ww",wk-1,dateserial(yr,01,01)), vbMonday),dateadd("ww",wk-1,dateserial(yr,01,01)))
Gustav BrockCIOCommented:
Sorry, got derailed by the other comments ...

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

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

mlcktmguyAuthor Commented:
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.
Thanks, glad to help

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now