Weeknum function issue

[WS]

Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows
week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't
be both 53 and 1

 

[Sheeloo]

That is the way it has been designed. Following is from the help for WEEKNUM;
""
The WEEKNUM function considers the week containing January 1 to be the first
week of the year. However, there is a European standard that defines the
first week as the one with the majority of days (four or more) falling in the
new year. This means that for years in which there are three days or less in
the first week of January, the WEEKNUM function returns week numbers that are
incorrect according to the European standard."

 

[Mike H]

Hi,

Straight from Excel Help

The WEEKNUM function considers the week containing January 1 to be the first
week of the year. However, there is a European standard that defines the
first week as the one with the majority of days (four or more) falling in the
new year. This means that for years in which there are three days or less in
the first week of January, the WEEKNUM function returns week numbers that are
incorrect according to the European standard.

Mike

 

[Mike H]

For the result I think you expect try this

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+
WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)

Mike

 

[Glenn]

Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows
week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't
be both 53 and 1

You could use something like this, which pulls the WEEKNUM for every day from
the preceding Sunday:

=WEEKNUM(A1-WEEKDAY(A1)+1)

Keep in mind that there will be no week number 1 for any year that doesn't start
on Sunday.

 

[Ron de Bruin]

Hi WS

See this page for more info
http://www.rondebruin.nl/weeknumber.htm

Or this MSDN page
http://msdn.microsoft.com/en-us/library/bb277364.aspx

 

[Kriss]

Hi

I have an similar problem.
However, I also want to display the year, including the week number.

My problem is;
31.12.09 = week 53 year 2009
01.01.10 = week 53 year 2010

But I want the entire week from 28.12.09 - 03.01.10 to be displayed as week
53-2009.

I hope anyone can help me on this matter.

 

[Ron Rosenfeld]

Hi

I have an similar problem.
However, I also want to display the year, including the week number.

My problem is;
31.12.09 = week 53 year 2009
01.01.10 = week 53 year 2010

But I want the entire week from 28.12.09 - 03.01.10 to be displayed as week
53-2009.

I hope anyone can help me on this matter.

If I understand you correctly, it seems you want to calculate the weeknumber
according to the ISO standard, which has week 1 starting on the Monday that
includes the first Thursday of the year.

The Excel WEEKNUM function does not make that calculation.

You can do it with a VBA UDF. To enter this, <alt-F11> opens the VBEditor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens. To use
this, enter a formula of the type =ISOWeeknum(cell_ref) into some cell, where
cell_ref contains a date.

Since you don't write how you want to display the year in this message, I can't
help you with that. But consider how you want to display the year for 01.01.10

===========================================
Function ISOWeeknum(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
ISOWeeknum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function
==========================================
--ron