Excel week number inconsistent results
Excel week number inconsistent results
I am using a spreadsheet that includes the week number for a particular date. After some research, I found a function ISOWEEKNUM() that I could apply to a column of dates and this appeared to work fine. An excerpt from this spreadsheet is shown in the link below:
However, I noticed that the function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1 when it always showed up as week 52 or week 53 in the previous years (which makes sense to me).
There are no other parameters than the date for this function, so it looks fairly straightforward. Any thoughts how to get this to work?
2 Answers
2
There is nothing wrong with your formula, but maybe it is not the formula you want to use. There is another Excel formula:
=WEEKNUM(serial_num, [return_type])
That will return the results that you expect to get. However, you should first check which type of week number is more appropriate to your application.
Let me explain further:
The ISOWEEKNUM(date) function works on the basis that a week always begins on a Monday and ends on a Sunday and then sets the standard that the first week of the year is that week which contains the first Thursday of the year in the week. This means that the last few days of the previous year can be labelled as week 1 of the next year.
The WEEKNUM(date, start_day) function starts counting on the week that contains January 1. So January 01 is the first day of Week 1. By default, new weeks begin on Sunday so Week 2 starts on the first Sunday after 01 January. You can change the start day using the second parameter in the function. In other words, the first week can have 1 day in it if 01 January is a Saturday and the default start day is used. <- this is the key understanding behind the WEEKNUM() function.
I created a small spreadsheet that contains your date values and adds a few more to demonstrate the difference:
Notice that it is not only 2018 where the last day in the year is in the first week of the next year. The ISOWEEKNUM() function is working fine, it just has a different interpretation of when the first week starts.
Another way of seeing this is by looking at the 2 functions over a day period that straddles the first of January:
In 2015, the first of January is a Thursday. The ISOWEEKNUM() week function therefore includes the last 3 days in December, while the WEEKNUM() function starts the week on the first of January but there will only be 3 days in the first week for the default start Day of Sunday.
I hope this explains the difference.
Thank you both for your feedback. Both answers have helped to understand why the answer received was correct. Indeed I needed weeknum() for my spreadsheet and therefor I chose @Clinton as the accepted answer. This has given me a clear understanding of the difference in the functions.
– WPDavid
Sep 9 '18 at 13:24
It is correct, as the 31st December 2018 is a Monday.
If 31 December is on a Monday, Tuesday or Wednesday, it is in week 01 of the next year. If it is on a Thursday, it is in week 53 of the year just ending; if on a Friday it is in week 52 (or 53 if the year just ending is a leap year); if on a Saturday or Sunday, it is in week 52 of the year just ending.
Source ISO week date - Wikipedia
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
Thanks for contributing an answer to Super User!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04