Calculating ISO Year

[DavidH56]

I'm looking for a formula to calculate the ISO year of a date in Excel. I came across this formula on Ron de Bruin's page to calculate the ISO week:

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

For 12/29/2013 the formula returns 52; I'm looking for a formula that would return "2013" for the year. For 12/30/2013 Ron's week formula returns 1; in that case the ISO year formula should return "2014".

I found this formula on Daily dose of Excel and I think it might work but I can't quite figure it out because of the html tags:

<span class="text">=YEAR(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)+<br>
         (7*IF(MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)>3,1,0))+7)</span>

Thanks in advance for the help!

David

 

[wigi]

Hi David

Almost there...

Here's the formula without tags, and operation on cell A1:

=YEAR(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,1)-MOD((DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,1)-2),7)+(7*IF(MOD((DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,1)-2),7)>3,1,0))+7)

 

[Andrew Poulsom]

If you have Excel 2010 or above you can use eg:

=WEEKNUM(A1,21)

 

[DavidH56]

Thanks Wigi, this is perfect...

I see that when I posted the message the Mr. Excel forum correctly translated the tags...

Thanks again!

 

[DavidH56]

Thanks Andrew, I'm currently using Excel 2007 but will be switching soon. Nice to know that Microsoft created a solution, thanks for letting me know.

 

[barry houdini]

Hello David,

If you only need the ISO year it's sufficient to find the Thursday of the current week and take the year of that, i.e. with date in A1 use

=YEAR(A1-WEEKDAY(A1,3)+3)

format as general

This will work with any version of excel

 

[mach128x]

Hello David,

If you only need the ISO year it's sufficient to find the Thursday of the current week and take the year of that, i.e. with date in A1 use

=YEAR(A1-WEEKDAY(A1,3)+3)

format as general

This will work with any version of excel

Exactly what I was looking for.
I was having some issues generating dates from the ISOWEEKNUM because I was using the actual year (YYYY part of the YYYY-MM-DD format) which is not always equal to the "ISO week-numbering year".
Thanks.