Excel week number inconsistent results
Clash Royale CLAN TAG#URR8PPP
up vote
18
down vote
favorite
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 at 12:32
DavidPostill♦
99.3k25210243
asked Sep 9 at 12:30
WPDavid
936
add a comment |Â
up vote
18
down vote
favorite
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 at 12:32
DavidPostill♦
99.3k25210243
asked Sep 9 at 12:30
WPDavid
936
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 at 17:04
add a comment |Â
up vote
18
down vote
favorite
up vote
18
down vote
favorite
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 at 12:32
DavidPostill♦
99.3k25210243
asked Sep 9 at 12:30
WPDavid
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 at 12:32
DavidPostill♦
99.3k25210243
asked Sep 9 at 12:30
WPDavid
936
edited Sep 9 at 12:32
DavidPostill♦
99.3k25210243
asked Sep 9 at 12:30
WPDavid
936
edited Sep 9 at 12:32
DavidPostill♦
99.3k25210243
edited Sep 9 at 12:32
DavidPostill♦
99.3k25210243
edited Sep 9 at 12:32
DavidPostill♦
99.3k25210243
99.3k25210243
asked Sep 9 at 12:30
WPDavid
936
asked Sep 9 at 12:30
WPDavid
936
asked Sep 9 at 12:30
WPDavid
936
936
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 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 at 17:04
4
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 at 17:04
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 at 17:04
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
33
down vote
accepted
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 spreadsheet.
Let me explain further:
The ISOWEEKNUM() 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.
The WEEKNUM(date, start_day) function starts counting with 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 from 2018 where the last day in the year is in the first week. 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 on the first of January but will only have 3 days in the first week for the default start Day of Sunday.
I hope this explains the difference.
answered Sep 9 at 13:08
Clinton
57228
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 at 13:24
add a comment |Â
up vote
16
down vote
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 at 12:40
DavidPostill♦
99.3k25210243
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
33
down vote
accepted
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 spreadsheet.
Let me explain further:
The ISOWEEKNUM() 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.
The WEEKNUM(date, start_day) function starts counting with 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 from 2018 where the last day in the year is in the first week. 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 on the first of January but will only have 3 days in the first week for the default start Day of Sunday.
I hope this explains the difference.
answered Sep 9 at 13:08
Clinton
57228
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 at 13:24
add a comment |Â
up vote
33
down vote
accepted
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 spreadsheet.
Let me explain further:
The ISOWEEKNUM() 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.
The WEEKNUM(date, start_day) function starts counting with 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 from 2018 where the last day in the year is in the first week. 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 on the first of January but will only have 3 days in the first week for the default start Day of Sunday.
I hope this explains the difference.
answered Sep 9 at 13:08
Clinton
57228
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 at 13:24
add a comment |Â
up vote
33
down vote
accepted
up vote
33
down vote
accepted
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 spreadsheet.
Let me explain further:
The ISOWEEKNUM() 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.
The WEEKNUM(date, start_day) function starts counting with 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 from 2018 where the last day in the year is in the first week. 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 on the first of January but will only have 3 days in the first week for the default start Day of Sunday.
I hope this explains the difference.
answered Sep 9 at 13:08
Clinton
57228
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 spreadsheet.
Let me explain further:
The ISOWEEKNUM() 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.
The WEEKNUM(date, start_day) function starts counting with 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 from 2018 where the last day in the year is in the first week. 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 on the first of January but will only have 3 days in the first week for the default start Day of Sunday.
I hope this explains the difference.
answered Sep 9 at 13:08
Clinton
57228
edited Sep 14 at 9:09
answered Sep 9 at 13:08
Clinton
57228
answered Sep 9 at 13:08
Clinton
57228
answered Sep 9 at 13:08
Clinton
57228
57228
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 at 13:24
add a comment |Â
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 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 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 at 13:24
add a comment |Â
up vote
16
down vote
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 at 12:40
DavidPostill♦
99.3k25210243
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
add a comment |Â
up vote
16
down vote
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 at 12:40
DavidPostill♦
99.3k25210243
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
add a comment |Â
up vote
16
down vote
up vote
16
down vote
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 at 12:40
DavidPostill♦
99.3k25210243
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 at 12:40
DavidPostill♦
99.3k25210243
answered Sep 9 at 12:40
DavidPostill♦
99.3k25210243
answered Sep 9 at 12:40
DavidPostill♦
99.3k25210243
answered Sep 9 at 12:40
DavidPostill♦
99.3k25210243
99.3k25210243
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
add a comment |Â
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
Blame Shanghai I say.
– Raystafarian
Sep 10 at 5:26
add a comment |Â
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
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
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
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
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
4
A week counts as part of the year the majority of that week is in.
– CodesInChaos
Sep 9 at 17:04