10-02-13, 10:15 #1Registered User
- 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
10-02-13, 11:55 #2Registered User
- 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.
10-02-13, 12:24 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if it were me I'd cheat and create a table that defined the financial year
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 bonusI'd rather be riding on the Tiger 800 or the Norton