Converting Dates into week numbers (custom)

[Bleep-Blop]

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

[alansidman]

Crossposted to: http://www.access-programmers.co.uk/...d.php?t=254432

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

[orange]

You may want to review the Datepart Function

see http://www.techonthenet.com/access/f...e/datepart.php

[ItsMe]

Bleep-Blop,

Have you had any luck with this?

[alansidman]

@ItsMe

Appears to have been solved in the other forum. To bad that the OP has not acknowledged anything about the crossposting.

[ItsMe]

Well I got tired of this thread not having some sort of solution posted. I built a calculator. It does not count the weeks from the last Sunday of December. This does not make sense to me. However, the code can be modified to count from whenever.

Weekday.zip

What it does:
The user submits a date and the calculator tells the user what week number that date falls in for that given calendar year. Example: 5/1/1997 falls within the 18th week of calendar year 1997.

How it works:
When the DB is opened a form opens asking the user for a date range. The user inputs a start date and the number of days (records) they want to add to the table. The user clicks the Run button.

The VBA then processes the user's request by populating a table with dates and the weekday for each date. The VBA uses the Weekday Function to determine the weekday for each date (record).

With the table populated, the calculater can query the table and count the number of weeks within a year for a given date.