ISO Dates

This page is based on ISO 8601
For ISO, all weeks commence on Mondays and end on a Sunday.
The days are numbered Monday = 1 through to Sunday = 7.
The first week of the year is the week in which 4 January falls. ISO actually talks about the first Thursday because it's the middle day of the ISO week, but remembering 4 January is easier for calculations. Or, to put it another way, the first week of the year contains at least four days of January.
The last week of the year is the week immediately preceding the first week of the next year!

To find the week number of a given date you can use the following formula, originally credited to Evert van den Heuvel.

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

As an alternative, you could use my version. Not much difference at first sight, but take a look at the use of the WEEKDAY function. Above, Evert uses the function's default setting which numbers the days as Sunday = 1 through to Saturday = 7. By using 2 as the second (optional) parameter it numbers the days from Monday = 1 to Sunday = 7.

What is the benefit of this? We are trying to calculate according to the ISO standard which numbers the days as Monday = 1 through to Sunday = 7. By using WEEKDAY's default setting we have to adjust for it's numbering scheme; by using the 2 argument, all calculations are now from the same base of Monday = 1.

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

The next formula shows how to calculate the week commencing date based on two inputs: week number and year. For this example, put the week number in cell A1, the year in cell A2, and the following formula in a convenient cell.

=DATE(B1,1,5)+((A1-1)*7)-WEEKDAY(DATE(YEAR(DATE(B1,1,5)+4-WEEKDAY(DATE(B1,1,5),2)),1,3)+1,2)

If you have Microsoft Office Web Components installed then by changing values in A1 and B1 you can see the result of the formula.
Please note that you can put in silly week numbers such as 54, 55 etc. Please don't!