Link to home
Create AccountLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Week Number Strangeness at year End/Begin

I created an app during the past year that utilizes the week number of data to combine results.  Since I am creating data that will span years and want to keep it sorted correctly I am using Year and Week instead of just week.  On any reports with year and week shown the user also wants to see the week ending date of the week as reference.

I am using the functions in the code window to generate the information shown in the following display generated from Sub 'callWW' :

Date: 12/22/2010  Week:  51  YearAndWeek:  201051  WeekEnd: 12/19/2010
Date: 12/23/2010  Week:  51  YearAndWeek:  201051  WeekEnd: 12/19/2010
Date: 12/24/2010  Week:  51  YearAndWeek:  201051  WeekEnd: 12/19/2010
Date: 12/25/2010  Week:  51  YearAndWeek:  201051  WeekEnd: 12/19/2010
Date: 12/26/2010  Week:  51  YearAndWeek:  201051  WeekEnd: 12/19/2010
Date: 12/27/2010  Week:  52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 12/28/2010  Week:  52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 12/29/2010  Week:  52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 12/30/2010  Week:  52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 12/31/2010  Week:  52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 1/1/2011  Week:      52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 1/2/2011  Week:      52  YearAndWeek:  201052  WeekEnd: 12/26/2010
Date: 1/3/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/4/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/5/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/6/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/7/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/8/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/9/2011  Week:        1  YearAndWeek:  201101  WeekEnd: 1/2/2011
Date: 1/10/2011  Week:      2  YearAndWeek:  201102  WeekEnd: 1/9/2011      

When I saw that 01/01/11 and 01/02/11 were being returned as week 52 I modified Function getYearAndWeekOfDate to return the year and week as 201052 instead of 201152.  My current issue is in the routine getEndDateOfYearAndWeek., the results of which are shown in the last column of the display.  There are many problems in that week ending column.  For example:

12/26/10 is shown as being in week 2010 with a week ending date of 12/19/10 which is prior to it.

12/27/10 thru 12/31/10 are shown as being in week 52 of year 2010.  However the week ending date of that week is calculated as 12/26/10, which is prior to all of those dates.

By the way, the users are fine with 1/1/11 and 1/2/11 being considered in week 52 of 2010 since the year week designation is just a grouping mechanism for them.  However, they are not fine with the way the week ending dates are being shown.

I'm not sure how to reconcile these differences.  Any ideas?

Private Sub callWW(passedDate As Date)
'
Dim wkWW As Long
Dim wkYYYYWW As Long
'
'wkYYYYWW = getYearAndWeekOfDate(passedDate)
wkWW = getWeekOfDate_2(passedDate)
wkYYYYWW = getYearAndWeekOfDate(passedDate)
'
Debug.Print "Date: "; passedDate; " Week: "; wkWW; " YearAndWeek: "; wkYYYYWW; " WeekEnd: "; getEndDateOfYearAndWeek(wkYYYYWW)
'
End Sub
'/////////////////////
Public Function getWeekOfDate_2(passedDate As Variant) As Long
'
getWeekOfDate_2 = 0
'
If Not IsDate(passedDate) Then
    Exit Function
End If
'
If IsNull(passedDate) Then
    Exit Function
End If
'
If IsEmpty(passedDate) Then
    Exit Function
End If
'
getWeekOfDate_2 = DatePart("ww", passedDate, vbMonday, vbFirstFullWeek)
'
End Function
‘/////////////////////////////////
Public Function getYearAndWeekOfDate(passedDate As Variant) As Long
'
Dim wkYearOfDate As Long
Dim wkWeekOfDate As Long
Dim wkMonthOfDate As Long
Dim wkYearPrefix As Long
'
getYearAndWeekOfDate = 0
'
If Not IsDate(passedDate) Then
    Exit Function
End If
'
If IsNull(passedDate) Then
    Exit Function
End If
'
If IsEmpty(passedDate) Then
    Exit Function
End If
'
wkYearOfDate = DatePart("yyyy", passedDate, vbMonday, vbFirstFullWeek)
'
wkMonthOfDate = DatePart("m", passedDate, vbMonday, vbFirstFullWeek)
'
wkWeekOfDate = DatePart("ww", passedDate, vbMonday, vbFirstFullWeek)
'
' handle January dates from new year that are part of weeks in the old year due to specifying
' the start of the year in the above statement as vbFirstFullWeek.  Using that specification can
' put Jan 1, 2 etc in the last week of the prior year.
'
If wkMonthOfDate = 1 Then
    If wkWeekOfDate > 7 Then
        wkYearOfDate = wkYearOfDate - 1
    End If
End If
'
wkYearOfDate = wkYearOfDate * 100
'
getYearAndWeekOfDate = wkWeekOfDate + wkYearOfDate
'
End Function
‘//////////////////////////////////
Public Function getEndDateOfYearAndWeek(passedYearAndWeek As Long) As Date
'
Dim wkYear As Long

wkYear = Val(Mid(Trim(Str(passedYearAndWeek)), 1, 4))
'
Dim wkWeek As Long
wkWeek = Val(Mid(Trim(Str(passedYearAndWeek)), 5, 2))
'
getEndDateOfYearAndWeek = cBadDate
'
If wkWeek < 1 Then
    Exit Function
End If
'
getEndDateOfYearAndWeek = DateAdd("d", 7 - DatePart("w", DateAdd("ww", wkWeek - 1, DateSerial(wkYear, 1, 1)), vbMonday), DateAdd("ww", wkWeek - 1, DateSerial(wkYear, 1, 1)))
'
End Function

Open in new window

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<I'm not sure how to reconcile these differences.  Any ideas?>>

  First thing to understand is that if you pick a specific day on the calendar and count the weeks according to that, every seven years you will have one year that has 53 weeks in it.

  The result your seeing are a result of that.  How you compensate is really up to you.

JimD.

 I should add that most applications allow for entry of a "fiscal calendar", which has the starting date of each period, the ending date of the fiscal year, and the first week ending date of the year, which is used to calculate the weeks and where they fall.

JimD.
mlcktmguy,

I always recommend creating your own table to specify things like week definitions.  For example:

tblWeeks

WeekStart     WeekEnd       WeekLabel
-------------------------------------
2010-01-04    2010-01-10    201001
2010-01-11    2010-01-17    201002
2010-01-18    2010-01-24    201003
2010-01-25    2010-01-31    201004
2010-02-01    2010-02-07    201005
2010-02-08    2010-02-14    201006
2010-02-15    2010-02-21    201007
2010-02-22    2010-02-28    201008
2010-03-01    2010-03-07    201009
2010-03-08    2010-03-14    201010
2010-03-15    2010-03-21    201011
2010-03-22    2010-03-28    201012
2010-03-29    2010-04-04    201013
2010-04-05    2010-04-11    201014
2010-04-12    2010-04-18    201015
2010-04-19    2010-04-25    201016
2010-04-26    2010-05-02    201017
2010-05-03    2010-05-09    201018
2010-05-10    2010-05-16    201019
2010-05-17    2010-05-23    201020
2010-05-24    2010-05-30    201021
2010-05-31    2010-06-06    201022
2010-06-07    2010-06-13    201023
2010-06-14    2010-06-20    201024
2010-06-21    2010-06-27    201025
2010-06-28    2010-07-04    201026
2010-07-05    2010-07-11    201027
2010-07-12    2010-07-18    201028
2010-07-19    2010-07-25    201029
2010-07-26    2010-08-01    201030
2010-08-02    2010-08-08    201031
2010-08-09    2010-08-15    201032
2010-08-16    2010-08-22    201033
2010-08-23    2010-08-29    201034
2010-08-30    2010-09-05    201035
2010-09-06    2010-09-12    201036
2010-09-13    2010-09-19    201037
2010-09-20    2010-09-26    201038
2010-09-27    2010-10-03    201039
2010-10-04    2010-10-10    201040
2010-10-11    2010-10-17    201041
2010-10-18    2010-10-24    201042
2010-10-25    2010-10-31    201043
2010-11-01    2010-11-07    201044
2010-11-08    2010-11-14    201045
2010-11-15    2010-11-21    201046
2010-11-22    2010-11-28    201047
2010-11-29    2010-12-05    201048
2010-12-06    2010-12-12    201049
2010-12-13    2010-12-19    201050
2010-12-20    2010-12-26    201051
2010-12-27    2011-01-02    201052
2011-01-03    2011-01-09    201101
2011-01-10    2011-01-16    201102
2011-01-17    2011-01-23    201103
2011-01-24    2011-01-30    201104
2011-01-31    2011-02-06    201105
2011-02-07    2011-02-13    201106

Open in new window


Now you can use that in a query like this:

SELECT s.SaleDate, s.SaleAmount, w.WeekLabel, w.WeekStart, w.WeekEnd
FROM tblSales s INNER JOIN
    tblWeeks w ON s.SaleDate >= w.WeekStart And s.SaleDate <= w.WeekEnd
ORDER BY s.SaleDate

Open in new window


Patrick

 and note that the DatePart Function has firstdayofweek and firstweekofyear to make adjustments in the week # returned.  You may want to play around with those.

JimD.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Gustav,

This issue comes up rather frequently, and your approaches to date problems generally are usually excellent.  I think the EE community at large would stand to gain a lot if you would put some of that wisdom into an article (or even a series of articles).

As an inducement to you, you would gain points almost like "royalties" whenever you or other Experts refer to your article(s) in accepted answers...

Patrick
Avatar of mlcktmguy

ASKER

I' am increasing the points so I can award more to cactus_data.  I was looking for some ideas and he went way beyond that and provided a turn key, all inclusive solution that completely resolved my issues.  It saved me much time.
Outstanding response, a complete solution to all my issues.
Thank you! You are welcome.

Patrick, you are right, I should do that, and maybe some day I will, but free time is so limited ...

/gustav