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
=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