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 !

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.

--
Gary''s Student - gsnu201001


"The Fool on the Hill" wrote:

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 !

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" wrote:

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 !

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

On Thursday, February 11, 2010 8:05 AM The Fool on the Hill wrote:

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 !


On Thursday, February 11, 2010 8:26 AM Gary''s Student wrote:

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.

--
Gary''s Student - gsnu201001


"The Fool on the Hill" wrote:


On Thursday, February 11, 2010 8:28 AM Eduardo wrote:

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" wrote:


On Thursday, February 11, 2010 9:14 AM Gary''s Student wrote:

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

http://www.cpearson.com/excel/WeekNumbers.aspx
--
Gary''s Student - gsnu201001


"The Fool on the Hill" wrote:


On Thursday, February 11, 2010 11:19 AM Ron de Bruin wrote:

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm