ISO week number

maybe you accidentally set the first day argument wrong?:

hacke wrote:

In Numbers I haven't found any way to calculate the week number according to the ISO standard used in almost all European countries. Is there any way to do this or is Excel the only work around?

There may be other ways, but you can calculate the ISO week number in Numbers the same way you could do it in Excel, as described here.

If the date is in B4 then you can get its ISO week number with this:

=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4 ),1,3))+5)/7)

SG

enter the date in column A

the function SG suggested is in column B

B2=INT((A2−DATE(YEAR(A2−WEEKDAY(A2−1,first-day)+4),1,3)+WEEKDAY(DATE(YEAR(A2−WEE KDAY(A2−1,first-day)+4 ),1,3),first-day)+5)÷7)

C2=WEEKNUM(A2,2)

D2=WEEKNUM(A2,1)

select B2 thru D2, copy

select B2 the the end of column D, paste

column B and C match

Here are the results of testing the suggested formula for 2016.

The suggested formula in column D matches A, the ISO week number. The built-in WEEKNUM function, either with Sunday is 1 or with Monday is 1, doesn't give the desired result.

SG

I was using 2015 for the year...

When I expand the table to go way in to 2016, the results do not match and the suggested formula from the website looks "weird":

I do not know what the "correct" answer is.

Hi Wayne,

The ISO week numbers in column A of my test table are what one can find posted on the Internet. The formula I suggested (from Microsoft, for Excel) produces results that match those week numbers.

So, despite apparent lingering doubts, the evidence strongly suggests that my suggested solution is indeed "correct."🙂

There may be more succinct formulas in Numbers using DURATION, but this one works in both Excel and Numbers.

SG

SG,

I was not trying to cast doubt as to your particular solution so much as wonder about the fact that the week number does not roll over to 1 at the beginning of a new year.

It must be that ISO requires the rollover to occur for the first full week where the other functions roll over at the beginning of the year. correct was in doubt quotes because the ISO result does not look correct IN MY OPINION. Knowing that there is some other definition of what correct is lead me to double quote correct.

Here the answer:

1. 8601

2. Week number according to the ISO-8601 standard, weeks starting on Monday. The first week of the year is the week that contains that year's first Thursday (='First 4-day week'). The highest week number in a year is either 52 or 53. This year has 53 weeks.

Wayne,

Yes, calendars can be complicated! I think 2020-2022 are the next years after 2016 where the ISO week number may become counter-intituitive.

SG

hacke wrote:

Thanks SGIII,

I hope that the next version of Numbers will be localized and not just translated. Excel actually return the correct week number in countries where ISO is used.

An ISOWEEKNUM() function was added in Excel 2013. Is that what you are referring to?

You could give feedback to Apple via your menu Numbers > Provide Numbers Feedback.

SG