Weeknum function giving me a 53rd week in the year! 2

[Louise99]

In column C I'm using the function =weeknum(b:1,2) where b:1 is the date. I want the week to start on Monday so I put the "2" in the parentheses.

Works just fine until I get to 12/25/06. Then I find the value "53" in the column! Why am I getting an extra week in my year (not that I couldn't use it to go shopping before Christmas).

Any suggestions greatly appreciated.

Thanks!

 

[razman10]

I don't know why Excel is reading it that way, but I can tell you it's because you are using Monday as your start day.

For example, 1/1/06 is coming up as week 1, but 1/2/06 is coming up as week 2.

Try changing your start day to 1 (Sunday). That should do the trick.

 

[SkipVought]

Hi,

It's just like, how can you get 5 weeks in a month?

5 * 7 = 35. No such month, eh?

Similar thing happens with weeks & years, only not as often as 5 with months.

If WEEK starts on Jan 1, then 52 * 7 days later its Dec 31, the beginning of the 53[sup]rd[/sup] week.

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

Why am I getting an extra week in my year

To elaborate on what Skip said....

Think about it. You are saying that the week starts on Monday.

Jan 1, 2006 was a Sunday. Being the first day of the year, that is the first week of the year.

Jan 2, 2006 is a Monday - and new weeks start on a Monday - so that's the second week of the year, even though it is only the second day.

Try changing your start day to 1 (Sunday). That should do the trick.

For 2006 it will. But what about 2007, which started on a Monday? Or next year when Jan. 1 is on a Tuesday?

The fact is, there are at least 53 week-divisions in a year, depending on the year and on what day of the week you decide to start counting. In fact, every 28 years there are 54 week-divisions. 2012 will be the next if your weeks start on Monday. 2000 and 2028 had 54 weeks if you go by Week Starting on Sunday.

What I do is report on Week Ending Dates. So if I'm reporting weekly data, then the 53rd week of 2005 (12/26-12/31) gets reported along with 1/1/06 as Week Ending 1/1/06.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.