Weeknum(today()) gives wrong weeknummer

[The Fool on the Hill]

Hello Excel-lent users,

I use Weeknum(today()) to determine which weeknumber is valid for today. The
problem is that for today 11 feb 2010 (02/11/2010 in us date config) it
returns with weeknumber 7 instead of week number 6. The same is applicable if
I use
weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2).

Can I rely on using weeknum(today())-1 or should I do something else?

Please help !

 

[Gary''s Student]

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.

 

[Eduardo]

Hi,
Is giving you 7 because is taking in consideration January 1 as a week, the
excel help says

Returns a number that indicates where the week falls numerically within a
year.

Important 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.

Syntax

Please it this helps click yes thanks

 

[The Fool on the Hill]

Hello Gary's Student,

The first three days of the year where fri 1st of jan till Sat 3 januari.
According to the european standard, this means that week 1 is actually 2.
Hence adding -1 in the formula makes sense (here in europe) ?

Thanks 4 your answer.

Kind regards,

Jan-Willem

 

[Gary''s Student]

It makes sense to me. For more information, see:

http://www.cpearson.com/excel/WeekNumbers.aspx

 

[Ron de Bruin]

See also
http://msdn.microsoft.com/en-us/library/bb277364.aspx

 

[Karsten Awizio]

Hi,
WEEKNUM provides american week number. Unfortunately, this is different from the european week number for about every second year, e.g. 2010 and 2011.
This formula provides european week number:
=WEEKNUM(A1;2)-1*(WEEKNUM(DATE(YEAR(A1);1;4);2)=2)
Sample: 01.01.2010 results in 0, 04.01.2010 results in 1.

Alternative solution to work with overlapping first week, i.e. avoidung the value 0 for the very first days of a year:
=TRUNC((A1-WEEKDAY(A1;2)-DATE(YEAR(A1+4-WEEKDAY(A1;2));1;-10))/7)
Sample: 01.01.2010 results in 53, 04.01.2010 results in 1.

Best regards, Karsten