tag:blogger.com,1999:blog-1971974841508744352.post7543697296340670981..comments2024-02-16T02:27:30.012-05:00Comments on Chatting about Excel and More: WEEKNUM()excel-divahttp://www.blogger.com/profile/13780428201816311547noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-1971974841508744352.post-54298925365528662862010-02-03T08:04:12.658-05:002010-02-03T08:04:12.658-05:00Thanks Jan-
For those of you not familiar- The Int...Thanks Jan-<br />For those of you not familiar- The International Standards Organization (ISO) defines an ISO week as always beginning of a Monday.<br />ISO‘s Week 1 is the first week of the month that has at least 4 days (one of which is Thursday since the week begins on a Monday).excel-divahttps://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-54597202666545584712010-02-03T05:16:42.583-05:002010-02-03T05:16:42.583-05:00For completeness sake, here is one way to calculat...For completeness sake, here is one way to calculate the ISO weekday from a date in cell A2:<br /><br />=1+INT((A2-DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,5)+WEEKDAY(DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,3)))/7)<br /><br />Alternatively, you can use a sligtly shorter array formula:<br /><br />=INT((A2-SUM(MOD(DATE(YEAR(A2-MOD(A2-2,7)+3),1,2),{1E+99;7})*{1;-1})+5)/7)<br /><br />And this returns the date of the monday in a specific ISO week (year in E1, weeknumber in E2):<br /><br />=DATE($E$1,1,1)+(E2-IF(WEEKDAY(DATE($E$1,1,1),2)<5,1,0))*7-WEEKDAY(DATE($E$1,1,1),2)+1Jan Karel Pietersehttp://www.jkp-ads.comnoreply@blogger.com