 
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((A1DATE(YEAR(A1+4WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4WEEKDAY(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((A1DATE(YEAR(A1+4WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4WEEKDAY(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)+((A11)*7)WEEKDAY(DATE(YEAR(DATE(B1,1,5)+4WEEKDAY(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! 


Published, 02 March 2003
Last updated
20 August 2011 13:58
