Converting Dates into week numbers

If the last sunday of the year is the 25th of December then that is week 53, if the last sunday is greater after the 25th then that sunday starts week 1 of the next year.

Code: 
Private Sub Command2_Click()
Dim Test As Date
Test = Text0

checker = Test - Weekday(Test) + 1  'finds the previous sunday
MsgBox checker
checker2 = Format(checker, "dd") 'gets the value of the date of that sunday
MsgBox checker2
If Month(checker) = 12 And checker2 = 25 Then 'checks if the sunday is the 25th of december

  YearNum = Year(Test) 'corrects accordingly
    WeekNum = 53
Answer = WeekNum & "-" & YearNum
MsgBox Answer
Else  'else it uses the getweeknum function
MsgBox getWeekNum(Test)
'MsgBox getWeekNum([Test])
   End If
End Sub

Still testing, but this one is looking better
 
You should be able to adapt my code to it. First extract the year (Y) of the date that you pass to it. Then throw in a test so that if the passed date is greater than or equal to 12/25/Y, if so then look at 12/25/Y to see if it occurs on a Sunday. If so, WeekNum=53.
 
yes, you're right I was thinking at the wrong end, you only need to check if DatePart returns 53
Code: 
Function DatePartCheck(dateChecked as Date) As Integer
Dim yearStart as Date
Dim yearCheck, i as Integer
i = DatePart("ww", dateChecked, vbSunday, vbFirstJan1)
If i = 53 Then
    yearCheck = Year(dateChecked) +1
    yearStart = Dlookup("startDate","lookupTable", "inYear = " & yearCheck)
    If dateChecked Between yearStart And CDate("31/12/" & Year(dateChecked)) Then: i =52
End If
DatePartCheck = i
 
End Function

lookupTable will something like this
startDate inYear
30/12/2012 2013
29/12/2013 2014
28/12/2014 2015
27/12/2015 2016
25/12/2016 2017
31/12/2017 2018
30/12/2018 2019

David
 
might need to replace
If dateChecked Between yearStart And CDate("31/12/" & Year(dateChecked)) Then: i =52
with
Code: 
If dateChecked >= yearStart And dateChecked <= CDate("31/12/" & Year(dateChecked)) Then: i =52
 
Code: 
Private Sub Command2_Click()
Dim Test As Date
Dim yy As Date
Test = Text0
 yy = Year([Test])
If Test >= "25/12" & yy Then
MsgBox Weekday("25/12" & yy)
If Day("25/12" & yy) = 25 Then
    WeekNum = 53
Answer = WeekNum & "-" & yy
MsgBox Answer
End If
  Else 'it uses the getweeknum function
MsgBox getWeekNum([Test])
End If


End Sub

it returns 53-2012 for 30/12/2012
when is should be 1-2013

I cant seem to execute what you proposed Plog. Have made any obvious errors?
 
Thanks david, I will write up a lookup table tonight then run your code, it looks promising :)

Thanks for all the advice so far :)
 
Code: 
Function DatePartCheck(dateChecked as Date) As Integer
Dim yearStart as Date
Dim yearCheck, i as Integer
i = DatePart("ww", dateChecked, vbSunday, vbFirstJan1)
If i = 53 Then
    yearCheck = Year(dateChecked) +1
    yearStart = Dlookup("startDate","lookupTable", "inYear = " & yearCheck)
    If dateChecked Between yearStart And CDate("31/12/" & Year(dateChecked)) Then: i =52
End If
DatePartCheck = i
 
End Function

Date checked = 29/12/2013 --- Should be week 1 of 2014

Code: 
?DatePart("ww", "29/12/13", vbSunday, vbFirstJan1)
 53

i = 53 Therefore

yearCheck = Year(2013) +1 = 2014
yearStart =Startdate where year = 2014--- = 28/12/2014

If 29/12/2013 >= 28/12/2014 and 29/12/2013 <= 31/12/2013 then i = 52

David, im not sure how this works outs. (apologies if i have followed the code in the wrong fashion) but the date checked will never be greater than YearStart as this is always the DateChecked year + 1. Also it doesnt convert the "wrong week 53" into week 1. However, this code seems very close to what i need. Thank you for sharing it :)

If i have made a mistake please let me know.

Examples of what weeks should be:

29/12/13
30/12/13
31/12/13
1/1/14
2/1/14
3/1/14
4/1/14

These above should be Week One according to my manager.

Code: 
?DatePart("ww", "29/12/13"datechecked, vbSunday, vbFirstJan1)

This gives me week 1 correctly for 1/1/14
2/1/14
3/1/14
4/1/14

And also follows that 5/1/14 is week 2 correctly.

I need to catch: 29/12/13
30/12/13
31/12/13

(given as week 53) and convert to week 1

Cheers
 
Random comment probably, but the datepart and format function as far as I know cover all possible combination used accross the globe ... why would your company use a weeknumbering that is different from anywhere else?

Having a weeknumbering inside a financial broken year, ok, feasable but something like this... trouble and nothing but trouble IMHO...
 
namliam said:
why would your company use a weeknumbering that is different from anywhere else?

Having a weeknumbering inside a financial broken year, ok, feasable but something like this... trouble and nothing but trouble IMHO...
Click to expand...

Yeah, tell me about it lol... :)

Basically this system is used to keep weeks "whole" for their absence stats.

in my example dates above - week 53 of 2013 is only 3 days long and week 1 of 2014 is 4 days long. They basically start the year 3 days early to capture 1 whole week.

Im being paid to program it., unfortunately they want to keep it how they want it :(

good question though.
 
So is the "normal" weeknumbering is something along the lines of
datepart("WW", AnyDate, vbSunday, vbFirstJan1)

except you want to pull any parts of the previous year into weeknumber 1, i.e. determine the weeknumber of any given saturday in this example, since the week starts at Sunday.

To calculate the weeknumber of any date's following saturday is something along the lines of
Code: 
datepart("WW", #12/30/2013# 
      - datepart("w", #12/30/2013#, vbSunday, vbFirstJan1) 
      + 7, vbSunday,vbFirstJan1)
#12/30/2013# the date you want to search for (2 times)

"- datepart("w", #12/30/2013#, vbSunday, vbFirstJan1) " move back to the previous Saturday
"+7" move to the next saturday instead
And always return the weeknumber of that saturday....

In essence collapsing any weekday sunday - friday to the next Saturday and giving it the weeknumber from that saturday.

In essence a "screwed up" self made version of "vbFirstFourDays" almost
 
I think i have it
Code: 
Public Function getWKN(test As Date) As Integer
Dim prevsun As Date


WKN = DatePart("ww", test, vbSunday, vbFirstJan1)

If WKN = 53 Then

prevsun = (test - Weekday(test) + 1)
If prevsun <> CDate("25/12/" & Year(test)) Then
WKN = 1
Else
WKN = 53
End If
End If
getWKN = WKN
End Function
 
Code: 
Public Function getWKN(test As Date)
Dim prevsun As Date
Dim YN As Date

WKN = DatePart("ww", test, vbSunday, vbFirstJan1)
iyear = Year(test)
MsgBox iyear
If WKN = 53 Then
prevsun = (test - Weekday(test) + 1)
If prevsun <> CDate("25/12/" & Year(test)) Then
WKN = 1
YN = DateAdd("yyyy", 1, test)
iyear = Year(YN)
End If
End If
getWKN = WKN & "-" & iyear
End Function

Checking this code above (looks good so far) :)

Thanks for all the help and advice so far.
 
Last edited:
namliam said:
So is the "normal" weeknumbering is something along the lines of
datepart("WW", AnyDate, vbSunday, vbFirstJan1)

except you want to pull any parts of the previous year into weeknumber 1, i.e. determine the weeknumber of any given saturday in this example, since the week starts at Sunday.

To calculate the weeknumber of any date's following saturday is something along the lines of
Code: 
datepart("WW", #12/30/2013# 
      - datepart("w", #12/30/2013#, vbSunday, vbFirstJan1) 
      + 7, vbSunday,vbFirstJan1)
#12/30/2013# the date you want to search for (2 times)

"- datepart("w", #12/30/2013#, vbSunday, vbFirstJan1) " move back to the previous Saturday
"+7" move to the next saturday instead
And always return the weeknumber of that saturday....

In essence collapsing any weekday sunday - friday to the next Saturday and giving it the weeknumber from that saturday.

In essence a "screwed up" self made version of "vbFirstFourDays" almost
Click to expand...

Did you try my "one line" solution?
 
namliam said:
Did you try my "one line" solution?
Click to expand...

Just finished testing it on a few items. I believe it works too :) ty very much :)

I will use this to get the weeknumber only. And my code to get the weeknumber + year.


Again, thank you very much :)

Iv stumbled, tripped and fell along the way while writing this code, but it is now solved :) Thanks to everyone for all the support and i apologize for my ignorance and misunderstandings :)
 
Instead of using datepart you can also use format:
Code: 
Format(#30-12-2013# - datepart("w", #30-12-2013#, vbSunday, vbFirstJan1) + 6, "WW", vbSunday,vbFirstJan1)
which can very easily be addapted to also return the "proper" year to the week 1....
Code: 
Format(#30-12-2013# - datepart("w", #30-12-2013#, vbSunday, vbFirstJan1) + 6, "YYYY-WW", vbSunday,vbFirstJan1)

Yes YYYY-WW since if you sort this you dont get week 2 of 2013 and 2014 put together
2-2013
2-2014
3-2013
but instead get the proper
2013-2
2013-3
2014-2
 
did you manage to sort this only I think I've figured out why datePart works but only partly. It works fine using
DatePart("ww", dateChecked, vbSunday, vbFirstJan1)
but the problem is, it won't recognise the start of a new year until Jan01 is reached, so the dates from the last Sunday of December up to 31st Dec won't trigger the new year start which is why you need to use the lookup table with a list of all the new year start dates and the year they relate to, so the method I suggested before should work with one small change

Code: 
Function DatePartCheck(dateChecked as Date) As Integer
Dim yearStart as Date
Dim yearCheck, i as Integer
i = DatePart("ww", dateChecked, vbSunday, vbFirstJan1)
If i > 51 Then
    yearCheck = Year(dateChecked) +1
    yearStart = Dlookup("startDate","lookupTable", "inYear = " & yearCheck)
    If dateChecked >= yearStart And dateChecked <= CDate("31/12/" & Year(dateChecked)) Then: i =1
End If
DatePartCheck = i
 
End Function

David
 
Hi David,

Yes it is solved :) I have adapted your idea of a lookup table and namliam's code to create a lookup table. I ran a simple loop function to record the date with its week number-year (1st day of the week) so i can easily convert a week number to a date and use that date+6 days as a range for that week :)

Thanks again for all the input, I would have still be bashing my head against the wall :banghead: attempting to solve this if i didnt have your help :)
 
You must log in or register to reply here.

Similar threads

Coxylaad
First and last day of week
Replies
6
Views
891
Coxylaad
Coxylaad
T
Can't get Proper Week Start Date
Replies
10
Views
964
theDBguy
theDBguy
Richard-B
Use of 7 day periods instead on weeks. (Biological recording d-base)
Replies
10
Views
1,215
jdraw
jdraw
N
Week number for start of 2021
Replies
3
Views
1,061
gemma-the-husky
gemma-the-husky
K
Autofil Fiscal Qtr number from a date
Replies
18
Views
1,277
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom