get week ending date from week

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
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?
Question by:mlcktmguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 11

Expert Comment

ID: 26283810
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.


Author Comment

ID: 26283912
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.
LVL 15

Expert Comment

ID: 26283974
How about this:

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

The week of the year is irrelevant for this

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 51

Expert Comment

by:Gustav Brock
ID: 26284132
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

LVL 44

Expert Comment

ID: 26284159
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.

Author Comment

ID: 26284331
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

LVL 44

Accepted Solution

GRayL earned 500 total points
ID: 26284504
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)))
LVL 51

Expert Comment

by:Gustav Brock
ID: 26284514
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


Author Comment

ID: 26286353
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.
LVL 44

Expert Comment

ID: 26286851
Thanks, glad to help

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question