ISO Weeknumber

[MartinL]

From what I have read, Excel power query does not have a built in date converter to ISO Week Number i.e. week 53 as happens every 6 years and used extensively in retail.

Has anyone had any experience in a work around for this.

example 27/12/2015 = week 53
and 7 days on 3/1/2016 = week 1

 

[thisoldman]

Starting with Excel 2011 for Mac and 2013 for Windows, there is an ISOWEEKNUM function.

Earlier versions can use this, with the date in A1:

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

 

[MartinL]

Starting with Excel 2011 for Mac and 2013 for Windows, there is an ISOWEEKNUM function.

Earlier versions can use this, with the date in A1:

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

The problem with this, is that it is not a PQ function.