Converting Dates into week numbers (1 Viewer)

bleep-blop · Oct 2, 2013

Hi,

Im trying to write some VBA to convert a date into a week number.

After a lot of studying and googling.

My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records (im the new guy) for a few years and deciphered this pattern:

I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.

Start day for the week is Sunday. End day is the Saturday.

Some examples to check against:

22nd December 2013 = Week 52 2013

29th December 2013 = Week 1 for 2014

----
21st December 2014 = Week 52 for 2014

28th December 2014 = Week 1 for 2015
----

20th December 2015 = Week 52 for 2015

27th December 2015 = Week 1 for 2016
----

25th December 2016 = Week 53 for 2016

1st Jan 2017 = Week 1 for 2017
-----

Any help would be greatly appreciated

Tried various methods already

iso 8601, wrong week start day

intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.

Thanks in advance

namliam · Oct 2, 2013

Check out the datepart and format functions in the help you can tell them what to do quite easily.

namliam · Oct 2, 2013

have you tried different values to the last parameter of your datepart? i.e. vbFirstFullWeek ?

bleep-blop · Oct 2, 2013

Hi,

Yeah, i have tried using datepart as i have said above. It doesnt give me the option that suits my requirements.

vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3

None of these result in the correct week number im looking for.

Alansidman · Oct 2, 2013

cross posted at: http://www.accessforums.net/programming/converting-dates-into-week-numbers-custom-38283.html

bleep-blop · Oct 2, 2013

Alansidman said:
cross posted at: accessforums.net/programming/converting-dates-into-week-numbers-custom-38283]

Is that an issue?

DavidAtWork · Oct 2, 2013

can't you use the DatePart function in conjuction with a table of Sunday dates that are the beginning dates of the new year.
Use DatePart to return a value (using the constant vbFirstJan1) then compare 1st Jan to the Sunday dates table and make an adjustment to the week number

David

bleep-blop · Oct 2, 2013

namliam said:
Check out the datepart and format functions in the help you can tell them what to do quite easily.

Some of my tests

Code:

?DatePart("ww", #12/25/2016#, vbSunday, vbFirstFourDays)
 52 
?DatePart("ww", #12/25/2016#, vbSunday,vbFirstFullWeek)
 52 
?DatePart("ww", #12/25/2016#, vbSunday, vbjan1)
 53 
?DatePart("ww", #12/27/2015#, vbSunday, vbjan1)
 53 
  ?DatePart("ww", #12/25/2016#, vbSunday,vbUseSystem)
 53 
 ?DatePart("ww", #12/27/2015#, vbSunday,vbUseSystem)
 53

The 1st one is wrong, the second is wrong too, The 3rd is right for the year 2016 but wrong for 2015. The final one has the same issues as vbjan1.

pr2-eugin · Oct 2, 2013

bleep-blop said:
Is that an issue?

Could be an Issue.

Alansidman · Oct 2, 2013

Bleep-blop

Please read this: http://www.excelguru.ca/content.php?184

bleep-blop · Oct 2, 2013

cross posted at" utteraccess Converting-Dates-Week-Nu-t2009192"

cant post links yet.

bleep-blop · Oct 2, 2013

DavidAtWork said:
can't you use the DatePart function in conjuction with a table of Sunday dates that are the beginning dates of the new year.
Use DatePart to return a value (using the constant vbFirstJan1) then compare 1st Jan to the Sunday dates table and make an adjustment to the week number

David

I'll look into that idea. Thanks david

plog · Oct 2, 2013

I'm agnostic as to if what you've defined is logical. I can't prove your definition will work in all cases, but you've done a great job of defining it, that I can't disprove it either. I do however, have a solution that works with all your test cases:

Code:

Public Function getWeekNum(d)
    ' returns Week and Year number of date (d): Day 1 of Week is first Sunday on or before January 1st
    
Dim DayOne As Date              ' holds first date of year (Sunday on or before 1/1)
Dim YearNum As Integer          ' holds Year number of date
Dim WeekNum As Integer          ' holds week number of date
YearNum = Year(d)
    ' default of year is year of date (d)
     
DayOne = CDate("1/1/" & YearNum)
If Weekday(DayOne) > 1 Then ret = DateAdd("d", (Weekday(DayOne) - 1) * -1, DayOne)
 ' gets date of sunday in first week of year
WeekNum = (DateDiff("ww", DayOne, d) + 1)
    ' gets difference in weeks between DayOne and date passed (d)
    
If (WeekNum = 53) And (YearNum Mod 4 > 0) Then
    ' if WeekNum is 53 and not leap year, rolls over return value to next year
    YearNum = YearNum + 1
    WeekNum = 1
    End If
    
getWeekNum = WeekNum & "-" & YearNum
End Function

Paste that into a module, then call it in a function like this:

WeekNum: getWeekNum([YourDateFieldHere])

It will return a value like this: '53-2016'. Again, it works for all the examples I've plugged into it, but I'm having a hard time convincing myself your definition works for every date.

Here's what I mean--you've essentially carved 4 years (365 + 365 + 365 + 366) into 209 weeks (52 + 52 + 52 + 53) and the math doesn't work on a day level. 4 years equals 1461 days and 209 weeks equals 1463 days. I'm almost certain this will break for some week at the end of some year--I just can't find that date yet.

bleep-blop · Oct 2, 2013

plog

You are an absolute legend!!!

The longevity of this system will be 10 years max ( so im going to check all several key dates up to 2023)

Also

(365 + 365 + 365 + 366)

I think it is because the week 1 can start in the previous year slightly so it covers 4 years and a bit, hence the 2 extra days.

As long as it in unbroken for 8-10 years im happy

Thanks soooo much. Im going to get cracking on testing it.

plog · Oct 2, 2013

I finally found where your methodology fails--the final week of a year that isn't preceded by a leap year and has its January 1st on a Saturday. For example, 2011 or 2022. Check out the calendar for 2022 and tell me what week and year 12/25/2022 to 12/31/2022 should go to?

http://www.timeanddate.com/calendar/?year=2022

Week 1 of Year 2022 starts on 12/26/2021, which makes 12/18/2022 the start of Week 52 of Year 2022. Following that, means 12/25/2022 should be Week 1 of Year 2023, but that puts the entire Week 1 of Year 2023 in actual calendar year 2022. Those 2 day differences I talked about do catch up with you. Your methodology creates a leap week.

bleep-blop · Oct 3, 2013

plog said:
I finally found where your methodology fails--the final week of a year that isn't preceded by a leap year and has its January 1st on a Saturday. For example, 2011 or 2022. Check out the calendar for 2022 and tell me what week and year 12/25/2022 to 12/31/2022 should go to?

Week 1 of Year 2022 starts on 12/26/2021, which makes 12/18/2022 the start of Week 52 of Year 2022. Following that, means 12/25/2022 should be Week 1 of Year 2023, but that puts the entire Week 1 of Year 2023 in actual calendar year 2022. Those 2 day differences I talked about do catch up with you. Your methodology creates a leap week.

Yeah, you are right, Thanks for spotting it. It also fails (so far) at 12/25/2033,

Im still trudging through checking all the results. Looking for a pattern.

I have checked manually the week numbers if you start week 1 2022 as 12/26/21 Then 12/25/22 should be week 53 as it its whole week is within the year. Im assuming that whenever it is sunday the 25th of december it is the start of week 53

Code:

 Date - Weekday(Date, vbMonday)

This finds the previous sunday, could that be used to check a date?

i.e. psuedocode

if (Date - Weekday(Date, vbMonday) ) = 12/25/**** then weeknum = 53 and yearnum = ****

meanwhile im searching through the dates to see if the above fails.

gemma-the-husky · Oct 3, 2013

i am sure you have got this solved, but the datepart and format functions ought to give you what you want, providing the method you use for determining which week is week 1 is one of the three options provided by Access.

if not, it becomes a trickier problem.

bleep-blop · Oct 3, 2013

Unfortunately the option in access do not

thanks for the support

DavidAtWork · Oct 3, 2013

I'm trying to understand why using DatePart function with constants vbSunday, vbFirstJan1 don't identify week 1 of the respective year, using this the last sunday of December will always be in the week Jan1 of the new year falls.
So assuming DatePart for now is correct, you only need to check if the value returned by DatePart is 1, because it could in fact be week 52/53 of the previous year, no. And that check can be done by having a lookup table with all the start date Sundays and a field with the number of weeks in that year

David

bleep-blop · Oct 3, 2013

DavidAtWork said:
I'm trying to understand why using DatePart function with constants vbSunday, vbFirstJan1 don't identify week 1 of the respective year, using this the last sunday of December will always be in the week Jan1 of the new year falls.
So assuming DatePart for now is correct, you only need to check if the value returned by DatePart is 1, because it could in fact be week 52/53 of the previous year, no. And that check can be done by having a lookup table with all the start date Sundays and a field with the number of weeks in that year

David

Code:

? DatePart("ww", "29/12/2013", vbSunday, vbFirstJan1)
 53

Gives me week 53-2013 when it should be week 1 of 2014, How would I adjust this using a lookup table?

Converting Dates into week numbers (1 Viewer)

Registered User.

The Mailman - AWF VIP

The Mailman - AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Super Moderator

AWF VIP

Registered User.

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread