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?
microsoft-excel microsoft-excel-2010 date
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
add a comment |
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?
microsoft-excel microsoft-excel-2010 date
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04
add a comment |
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?
microsoft-excel microsoft-excel-2010 date
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
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?
microsoft-excel microsoft-excel-2010 date
microsoft-excel microsoft-excel-2010 date
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
edited Sep 9 '18 at 12:32
DavidPostill♦
105k25227260
105k25227260
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
asked Sep 9 '18 at 12:30
WPDavidWPDavid
936
936
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04
add a comment |
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04
4
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
1
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
add a comment |
The function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1
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
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1356611%2fexcel-week-number-inconsistent-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
1
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
add a comment |
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.
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
1
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
add a comment |
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.
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
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.
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
edited Jan 11 at 9:14
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
answered Sep 9 '18 at 13:08
ClintonClinton
7091210
7091210
1
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
add a comment |
1
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
1
1
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
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
add a comment |
The function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1
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
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
add a comment |
The function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1
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
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
add a comment |
The function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1
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
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
The function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1
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
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
answered Sep 9 '18 at 12:40
DavidPostill♦DavidPostill
105k25227260
105k25227260
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
add a comment |
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
Blame Shanghai I say.
– Raystafarian
Sep 10 '18 at 5:26
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1356611%2fexcel-week-number-inconsistent-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 '18 at 17:04