Week of year and ISO8601

[sdawson]

Does anyone have code that will calculate the week of the year as per ISO8601?
viz. If 1st January is on a Friday, Saturday or Sunday then this is not week 1. It is week 52 or possibly week 53 of the previous year.

=Format(Now(), "ww") is displayed as week 1 even if 1st January is a Sunday!
This is for the week Monday to Sunday.


http://en.wikipedia.org/wiki/ISO_8601#Week_dates

This link may explain it better!

Help.

 

[sdawson]

Boys, oh boys, oh boys and ladies.
Surely I'm not the only Access user who has asked this!

 

[Rabbie]

Try this function

Public Function ISOWeekNo(dteDate As Date) As Integer
  Dim weekno As Integer
  
   If Weekday(DateSerial(Year(dteDate), 1, 1), vbMonday) > 4 Then
        weekno = Val(Format(dteDate, "ww")) - 1
        If weekno = 0 Then
            ISOWeekNo = Val(Format(DateSerial(Year(dteDate) - 1, 12, 31), "ww"))
        End If
   Else
        ISOWeekNo = Val(Format(dteDate, "ww"))
   End If
  
  End Function

 

[sdawson]

What can I say!
Thanks Rabbie.
I can now cheerfully go off to the local cinema to watch "Transformers, robots in disguise" with a smile on my face.

 

[Rabbie]

Enjoy the film. Of course there are no guarantees as to the accuracy of the code although I have tested it for dates in 2005 when 1st Jan was a Saturday. I advice you to check it is giving you the results you expect.

 

[Daveyk01]

Does anyone have code that will calculate the week of the year as per ISO8601?
viz. If 1st January is on a Friday, Saturday or Sunday then this is not week 1. It is week 52 or possibly week 53 of the previous year.

=Format(Now(), "ww") is displayed as week 1 even if 1st January is a Sunday!
This is for the week Monday to Sunday.


http://en.wikipedia.org/wiki/ISO_8601#Week_dates

This link may explain it better!

Help.

For what it may be worth, here is the code I have been using in our service database for the last couple of years and I have had no complaints from by boss or has to use ISO dates in his meetings with our German counterparts. I think I got the code from elsewhere in thise forum:

Public Function ISOWeekNum(AnyDate As Date, _
Optional WhichFormat As Variant) As Integer
'
' WhichFormat: missing or <> 2 then returns week number,
' = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then
Exit Function
End If
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
Format(ISOWeekNum, "00"))
End If

End Function

 

[sdawson]

Ah.
Not quite correct Rabbie.
Does not take into account that some days are at the beginning of the current year are in week 52 or 53 of previous year and the effect of a leap year.
It's a good start though. Thanks again.

 

[sdawson]

Thanks Daveyk01.
This is something like the code I knew I had seen on the forum somewhere.
Will test after the film.

Thanks again.

 

[Rabbie]

Ah.
Not quite correct Rabbie.
Does not take into account that some days are at the beginning of the current year are in week 52 or 53 of previous year and the effect of a leap year.
It's a good start though. Thanks again.

Not quite correct. If you input 1/1/2005 it gives the answer 53 which is what is required. The first if statement is all about the starting date of the year.

leap years are covered by the existing week number code in Access.

 

[sdawson]

I may have got that wrong Rabbie but the code never brings up week 52.
I've tried it backwards and forwards on no week 52s?

 

[Rabbie]

Darn it you're right. Good job I said there were no guarantees

Public Function ISOWeekNo(dteDate As Date) As Integer
  Dim weekno As Integer
  
   If Weekday(DateSerial(Year(dteDate), 1, 1), vbMonday) > 4 Then
        weekno = Val(Format(dteDate, "ww")) - 1
        If weekno = 0 Then
            ISOWeekNo = Val(Format(DateSerial(Year(dteDate) - 1, 12, 31), "ww"))
        Else
            ISOWeekNo = weekno
            
        End If
   Else
        ISOWeekNo = Val(Format(dteDate, "ww"))
   End If
  
  End Function

 

[Daveyk01]

Thanks Daveyk01.
This is something like the code I knew I had seen on the forum somewhere.
Will test after the film.

Thanks again.

We wanted to see the film also, but my wife and I took the kid to see the Simpsons. Of course our kid (5 years old) need a Soda and Popcorn. My wife and I just shared a medium soda. We spend $28US in our small town for all this!! The theator operator got upset when I asked him "Where are we? Neeeew Yorrrrk City?". But what they heck, that is a lot for a family movie that lasts an hour and 20 minutes! Then to top it all off, Homer calls everyone in the theator suckers for paying to see what you can see on TV for free! (I actually enjoyed that - the truth is funny).

The Transformers DVD will be out in three months, or probably for XMAS, and we can buy it then for $15US.

 

[sdawson]

Well Daveyk01, you missed a darn good film there mate.
Over 2 hours of mindless blow-em-up, gung ho stuff.
My kids (son 25 and daughter 28) took me ( although I had to pay a total of £7.50, about $3.70 for the three of us). A bargain.

Thanks again for the code.

 

[sdawson]

I remember now where I saw this.

http://www.cpearson.com/excel/weeknum.htm



The following function needs to be included to determine YearStart

Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If

End Function

 

[Daveyk01]

I remember now where I saw this.

http://www.cpearson.com/excel/weeknum.htm



The following function needs to be included to determine YearStart

Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If

End Function

Oops, sorry about that, but at least I got yaa pointed in the right direction.