Numbers formula for computing the iso week number for a date.

numbersIsoWeekFormula.txt

-- This formula assumes that the date is stored in A1

=QUOTIENT(DATEDIF(DATE(YEAR(A1 - WEEKDAY(A1,3)+3),1,1)-WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,3)+3),1,1),3),A1,"D"),7)+IF(WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1,3) + 3),1,1),2)>4,0,1)

-- Another, commented version of the formula:

-- The key trick of this formula is using thursday of the week the date falls in to determine which
-- ISO year we will fall into.  Many thanks to Jürgen Schell for this idea: 
--    http://www.j-schell.de/applescript_calendar_weeks_iso

-- The general strategy is this:
--  1) find the year the week falls in. It's not necessarily the same year as the date. May be one forward or one backward.
--  2) divide the span of days from year's beginning to the passed date by 7 to get weeks
--  3) determine whether to adjust for a week gobbled up in the previous year and do so
=
=
  QUOTIENT(
    DATEDIF(
                          -- Find the date for the first day of the week of the proper year
      DATE(               
        YEAR(
          A1              -- From the date given,
          - WEEKDAY(A1,3) -- goto Monday of that week,
          +3              -- then skip forward to Thursday
        ),                -- Take the year of that date
        1,
        1
      )                   -- All this to determin new years day of the proper year.
      -
      WEEKDAY(
        DATE(YEAR(A1-WEEKDAY(A1,3)+3),1,1), -- copy of the trick for finding New Years Day
        3                                   -- 0 = Monday, 1 = Tuesday, ...
      )   
                          -- All this to compute the date for the Monday of the week containing Jan 1 of the proper year.
      ,A1
      ,"D"
    ),                    -- Number of days since Monday of the week containing Jan 1 of the proper year
    7
  )                       -- weeks since the one containing Jan 1 of the proper year (0, 1, 2, ...)

    -- At this point we have the number of weeks between the date and the week containing Jan 1 of the ISO year. HOWEVER, that
    -- first week may not belong to the determined ISO year. It may have gone to the previous year. If it went to the previous
    -- year then we already have the right week number. If it goes to this year, then we must add 1 to our quotient.
  +
  IF(
    WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1,3) + 3),1,1),2)>4, -- Was Jan 1 a Fri, Sat, or Sun?
    0, -- Yes? Add nothing. Done.
    1  -- No? Then must add one to the quotient.
  )

-- The solution isn't pretty. I'd love to find a more elegant solution. It does work in all the test cases I've tried, and
-- several other solutions have had bugs near year ends.

Updates:

October 10, 2013 3:46 PM Added clause to move DATEDIF to Monday of the week containing Jan 1. Fixed an error and eliminated
the goofy week shifting business.