Abnormal results with Excel Weeknum ?

[u473]

I have been using Weeknum for some time.
The second parameter is to define whether the week starts on Monday(2)
or Sunday(1)

The first of the year 2006 was a Sunday.
Entering =weeknum(1/7/2006,2) which is a Saturday in cell A2 produced
1. And I agree.

Entering 1/7/2006 in cell A1 and weeknum(A1,2) in cell A2 produced 2
???.

Entering 12/25/2006 which is a Monday, in cell A1 and weeknum(A1,2) in
cell A2 produced 53 ???.

Entering =weeknum(12/25/2006,2) in cell A2 produced 1. That is weird.
You would expect 52.


Am I going to be obliged to use an If... to produce the expected
result ?
Can you give me some light ?

Thank you,

Wayne

 

[Bob Phillips]

Depends what you mean by expected results. Those are the expected results
for the Excel Weeknum function.

Read up at http://www.rondebruin.nl/weeknumber.htm

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ron Rosenfeld]

I have been using Weeknum for some time.
The second parameter is to define whether the week starts on Monday(2)
or Sunday(1)

The first of the year 2006 was a Sunday.
Entering =weeknum(1/7/2006,2) which is a Saturday in cell A2 produced
1. And I agree.

Entering 1/7/2006 in cell A1 and weeknum(A1,2) in cell A2 produced 2
???.

Entering 12/25/2006 which is a Monday, in cell A1 and weeknum(A1,2) in
cell A2 produced 53 ???.

Entering =weeknum(12/25/2006,2) in cell A2 produced 1. That is weird.
You would expect 52.


Am I going to be obliged to use an If... to produce the expected
result ?
Can you give me some light ?

Thank you,

Wayne

The WEEKNUM function is returning results as it is documented to do so.

The "light" if you will is how you are implementing the formula.

Within a function, the value: 1/7/2006 does not get translated to a date.
Rather it is translated as 1 divided by 7 divided by 2006 -- some very small
number equivalent to Saturday January 0, 1900

When you enter 1/7/2006 into a cell, then Excel translates it to the date
Saturday, January 07, 2006

This is why you get different answers to your two functions.

=weeknum(1/7/2006,2) is equivalent to

=WEEKNUM(DATE(1900,1,0),2)

and

=weeknum(a1,2)

is equivalent to

=WEEKNUM(DATE(2006,1,7),2)


--ron