Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013

    Unanswered: Converting Dates into week numbers


    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

  2. #2
    Join Date
    Oct 2013

    Converting Dates into week numbers


    I think you need to revisit your definition of "week". Since there are 365 days in a non-leap year, dividing a non-leap year into 52 weeks means having an 8 day week or leaving out a day.


  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if it were me I'd cheat and create a table that defined the financial year
    periodend date
    weekno integer

    and then join to that table where the datecolumn <= periodend
    you could write some function which finds the first sunday in December (usning the weekday function) then user an integer divide to give the weekno, eg:-
    weekno = ((mydate - firstsunindec) \ 7) +1

    but I#'d prefer to go down the day route as it shifts ownership of the data to the consumers of the data so if the weekno calculation goes wrong then its down to the owners of the data to sort out. its precisely the sort of mind-numbing boring work that appeals to accountants and lets face it anything that keeps them occupied away from damaging the actual business has got to be a bonus
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts