# Thread: Converting Dates into week numbers

1. Registered User
Join Date
Jul 2013
Posts
1

## Unanswered: Converting Dates into week numbers

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.

2. Registered User
Join Date
Oct 2013
Posts
8

## Converting Dates into week numbers

Hello,

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.

Cheers

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
if it were me I'd cheat and create a table that defined the financial year
eg
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

#### Posting Permissions

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