WEEKNUM Returning 53 Instead of 1

[bonzo]

For Sun, 12/29/02, Mon, 12/30/02, and Tue, 12/31/02, the WEEKNUM() worksheet function is returning 53 instead of 1. The dates for the rest of this week are all returning a 1.

To compensate for this, I have the following:

=IF(WEEKNUM(A1)=53,1,WEEKNUM(A1))

but I have to go through and change all of the cells that have WEEKNUM() to have the above conditional.

Is there already a recommended way to do this? Or, is there a way, perhaps, to override the WEEKNUM() worksheet function with my own version of WEEKNUM()?

Thanks...

 

[maxflia10]

From the help files on Weeknum

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

So it should return 53

 

[Aladin Akyurek]

Book13
	A	B	C	D	E	F
1	12/29/02	52	52	52	52
2	12/30/02	1	52	1	1
3	12/31/02	1	53	1	1
4
Sheet1

ISO.WEEKNUM is part of the morefunc.xll add-in.

The other formulas by different authors are presented along with a discussion of the issues involved at

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

 

[XL-Dennis]

Hi Bonzo,

Which weeknumber is correct?
It´s depend on where You live in the world.

For US-people the correct weeknumber is 53, which also the XL-built-in weeknumber-function also calculate with.

For me in Sweden the correct weeknumber is 1.
For You it seems also that the correct weeknumber should be 1.

Yes, we can start a long thread about ISO-weeks and so on but I will not do that.

To compensate it and get the correct week-number use following function:

<pre>
Option Explicit

Function WEEKNR(Datum As Date) As Integer
Dim lnDatum As Long

lnDatum = DateSerial(Year(Datum - Weekday(Datum - 1) + 4), 1, 3)

WEEKNR = Int((Datum - lnDatum + Weekday(lnDatum) + 5) / 7)

End Function
</pre>

I guess that we can´t make a shorter function then above :wink:

Kind regards,
Dennis

 

[Jack in the UK]

[No message]