[ September 15 2016 ]      AddinBox site moved from [ DION ] to [ SAKURA Internet ].  
      In addition, the old site remains until October 2017.
      New URL of this page http://addinbox.sakura.ne.jp/Excel_Tips20E.htm
 

  Tips20: WEEKNUM function
                        and ISO8601 week-numbering

      ( Because I who am poor at English am translating into English while using translation software,  
        there may be an odd expression. The mistranslation revises it sequentially. )

  (Jpn. 1st Edition : 14 Mar. 2010 )
  (Eng. Translation: 10 Aug. 2014 )
  (Jpn. & Eng. 2nd Edition : 10 July 2015 )
The WEEKNUM function of Excel calculates the week number (1-54). You can choose
"Starting on Sunday" or "Starting on Monday" with the second argument. However, the
last week of the previous year or the first week of the New Year may do not reach seven
days with the WEEKNUM function of Excel,

It prescribes a calculation method as ISO8601:Week-numbering in ISO.
      (a) The week is "Starting on the Monday".
      (b) The first week of the year is seven days (Starting on Monday) including the
          first Thursday of January or including January 4.
                    (These two expression is synonymous.  This means follows.
                    About a boundary week from the previous year to the New Year, all seven days (Starting
                    on Monday) belong towards the year that the majority (more than four days) belongs to.
)
      (c) In ISO8601, all weeks are seven days.
              (A boundary week from the previous year to the New Year may do not reach seven days
              with WEEKNUM function of Excel.)


        (Note)
          The definition of the ISO standard is only "Starting on Monday". However, "Starting on Sunday"
          is common in the week in Japan. You can calculate the week number (Starting on Sunday) by
          a definition similar to ISO8601 (See below for further details).
          In this case, it is defined the follows.
              The first week of the year is seven days including the first Wednesday of January or
              including January 4. (Starting on Sunday.  The part that seven days including January 4
              is the first week does not change.)


--- Support states by Excel of ISO8601 ---
    Before Excel2007
          ISO8601 is not supported.
          ( Please use the formula by Frank Kabel. )

    Excel2010
          ISO8601 is supported by WEEKNUM function.
          (appoint 21 with 2nd argument [return_type])

    Excel2013
          It is supported by ISOWEEKNUM function for ISO8601.
          Even WEEKNUM function (2nd argument is 21) is supported sequentially.

    VBA (All version)
          ISO8601 is supported by DatePart and Format function.
          (It becomes the week number of "Starting on Sunday" when you change
          the 3rd argument into vbSunday.)

                DatePart ( "ww", date, vbMonday, vbFirstFourDays )
                CInt( Format ( date, "ww", vbMonday, vbFirstFourDays ) )

          The calculation of DatePart and Format function include a bug.
          [ KB 200299 ] BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year

[ Link to here ]

The following formula (developed by the late Frank Kabel) returns the week number
for the date in cell A1 based on the ISO standard.
( refer to:MSDN : Implementing Week-Numbering Systems in Excel )

  [ Starting on Monday ]
    =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)
            +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)



[ Link to here ]

I process the formula by Frank Kabel and will make the formula of the week
number (Starting on Sunday).

[Starting on Monday]  by Frank Kabel
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)
            +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)


This formula calculates the week number of "Starting on Monday" using the weekday
number of "Starting on Sunday" (Sun[1], Mon[2], .... Sat[7]).

At first I will process this formula as follows.
        Calculates the week number of "Starting on Monday"
            using the weekday number of "Starting on Monday"

By WEEKDAY(DATE - 1, 1)
  It is able to calculates the weekday number of "Starting on Monday" (Mon[1], Tue[2],
  ... Sun[7]) using WEEKDAY function of "Starting on Sunday" (WEEKDAY( ,1) ).

Conversely, by WEEKDAY(DATE + 1, 2)
  It is able to calculates the weekday number of "Starting on Sunday" (Sun[1], Mon[2],
  ... Sat[7]) using WEEKDAY function of "Starting on Monday" (WEEKDAY( ,2) ).

Using this conversion, I will process it to describe the formula mentioned above with
the weekday number of "Starting on Monday".

  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)
        +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)
       
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1+1,2)+4),1,3)
        +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1+1,2)+4),1,3)+1,2)+5)/7)
       
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,3)
    +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,3)+1,2)+5)/7)
     [ The week number of "Starting on Monday" using the weekday number of "Starting on Monday". ]

    As for the following formula, it becomes the same meaning and the same result (See below).
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4)
    +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2)+6)/7)

    ( addition : 10 July 2015)
    And, as for the following formula (developed by barry houdini ), it gets the same result (See below).
  =INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4))/7)+2
    It first appeared in   16 May 2006   MrExcel.com / Year-Week Number.



It can calculate the week number of "Starting on Sunday" when you change into the
weekday number of "Starting on Sunday" ( WEEKDAY( ,1) ).
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,3)
    +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,3)+1,1)+5)/7)
     [ The week number of "Starting on Sunday" using the weekday number of "Starting on Sunday". ]

    As for the following formula, it becomes the same meaning and the same result (See below).
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,4)
    +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,4),1)+6)/7)

    ( addition : 10 July 2015)
    And, as for the following formula (developed by barry houdini ), it gets the same result (See below).
  =INT((A1-WEEKDAY(A1,1)-DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,4))/7)+2
    It first appeared in   16 May 2006   MrExcel.com / Year-Week Number.

  (Note)
        The week number of ISO8601 is only "Starting on Monday".
        "Starting on Sunday" is expansion to be off the standard of ISO8601.




The follows are user definition functions in VBA.

 Function ISO_WEEKNUM(ByVal argDate As Date, _
                      ByVal arg1stDayWeek As Integer) As Integer
 '[Created By Frank Kabel , Modified By K.Tsunoda]
 'http://msdn.microsoft.com/en-us/library/bb277364.aspx
 'http://www.h3.dion.ne.jp/~sakatsu/Excel_Tips20E.htm
 '
 '[arg1stDayWeek] 1(vbSunday): Sun to Sat , 2(vbMonday): Mon to Sun

 Dim dtmYY0104 As Date
   dtmYY0104 = _
        DateSerial(Year(argDate - Weekday(argDate,arg1stDayWeek) + 4),1,4)

   ISO_WEEKNUM = _
        (argDate - dtmYY0104 + Weekday(dtmYY0104,arg1stDayWeek) + 6) \ 7
 End Function


 Starting on Monday :  =ISO_WEEKNUM( A1, 2 )
 Starting on Sunday :  =ISO_WEEKNUM( A1, 1 )    [ It is off the standard of ISO8601. ]



[ Link to here ] [ News of the site move ]

Finally, I will analyze the structure of the formula of Frank Kabel.

Here, I analyze it from the processed formula mentioned above (the week number
of "Starting on Monday" using the weekday number of "Starting on Monday").
-- Condition --
      The first week of the year is seven days including the first Thursday
      of January or including January 4 (Starting on Monday).

  1     2     3     4     5     6     7    ... Number of weekday: WEEKDAY( ,2)
[Mon] [Tue] [Wed] [Thu] [Fri] [Sat] [Sun]
 1/1    2     3    (4)    5     6     7    ... 1st week of new year(Jan.)
12/31 1/1     2     3    (4)    5     6    ... 1st week of new year(Jan.)
12/30  31   1/1     2     3    (4)    5    ... 1st week of new year(Jan.)
12/29  30    31   1/1     2     3    (4)   ... 1st week of new year(Jan.)
12/28  29    30    31   1/1     2     3    ... Last week of previous year(Dec.)
12/27  28    29    30    31   1/1     2    ... Last week of previous year(Dec.)
12/26  27    28    29    30    31   1/1    ... Last week of previous year(Dec.)


Formula of Frank Kabel. (it has been processed to the weekday number of "Starting on Monday")
=INT((A1 - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 3)
        + WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 3) + 1, 2) + 5) / 7)

At first, about this part ...
      DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 3)

[A] : A1 - WEEKDAY(A1, 2)        The end of previous week of A1 (i.e. Sunday)
[B][A] + 4                                4 days later of Sunday (i.e. Thursday in the week of A1)
    [C] : DATE(YEAR([B]), 1, 3)      Jan. 3 of the year of Thursday in the week of A1
                              (In the figure mentioned above, the year of December or the year of January)

Next, about the whole that gave the conversion mentioned above ...
=INT((A1 - [C] + WEEKDAY([C] + 1, 2) + 5) / 7)

[D][C] + 1                                 Jan. 4 of the year of Thursday in the week of A1
    [Dw] : WEEKDAY([D], 2)    Weekday of Jan. 4 of the year of Thursday in the week of A1

=INT((A1 - [C] + [Dw] + 5 ) / 7)
   =INT((A1 - ([C] - [Dw] - 5) ) / 7)
     =INT((A1 - (( [C] + 1) - [Dw] - 6) ) / 7)
       =INT((A1 - ([D] - [Dw] - 6) ) / 7)
         =INT((A1 - ([D] - [Dw]) + 6) / 7)

[E] : ([D] - [Dw])   The end of previous week of [Jan. 4 of the year of Thursday in the week of A1]
                                  (i.e. Sunday and Base point of the days in total)
   =INT((A1 - [E] + 6) / 7)

[X] : A1 - [E]                      The days to A1 in total
   =INT(([X] + 6) / 7)     Divide the days in total by 7 and round it to the week number (1-53)



I finally understood the implication of the formula.
Next, I will build the formula from the specifications adversely.
  (a) Calculate the end of previous week of [Jan. 4]. (i.e. Sunday)
  (b) Calculate the days in total that subtracted the end of previous week of (a) from A1.
  (c) Divide the days in total by 7 and calculate the week number.
  (d) The year of [Jan. 4] is the year of Thursday in the week of A1.


Base point of the days in total : the end of previous week of [Jan. 4] (i.e. Sunday).
=DATE(year, 1, 4) - WEEKDAY(DATE(year, 1, 4), 2)

Calculate the days in total that subtracted the end of previous week from A1.
=A1 - (DATE(year, 1, 4) - WEEKDAY(DATE(year, 1, 4), 2))
   =A1 - DATE(year, 1, 4) + WEEKDAY(DATE(year, 1, 4), 2)

Divide the days in total by 7 and calculate the week number.
=INT( (A1 - DATE(year, 1, 4) + WEEKDAY(DATE(year, 1, 4), 2) + 6) / 7) .... Formula [1]

Calculate in the following formula about [ year ].
Thursday in the week of A1    The end of previous week of A1 (i.e. Sunday) + 4
=(A1 - WEEKDAY(A1, 2)) + 4

The year of [Jan. 4] is the year of Thursday in the week of A1.
[year]    YEAR(A1 - WEEKDAY(A1, 2) + 4)

Insert the formula of [year] mentioned above in Formula[1].
  [ISO8601 Week number]     (Starting on Monday)
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4)
      +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2)+6)/7)


  (Note)
    About the week number of "Starting on Sunday", it changes the number
    of weekday into "Starting on Sunday" (it is off the standard of ISO8601).
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,4)
      +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,1)+4),1,4),1)+6)/7)

 

It is slightly more different than the formula of Frank Kabel (it has been processed
to the weekday number of "Starting on Monday").
  =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,3)
      +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,3)+1,2)+5)/7)


You will understand that it is the same formula when you process it as follows.

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



Frank Kabel builds the formula to calculate the week number of "Starting on Monday"
using the weekday number of "Starting on Sunday". The following contents would be
left in a process of the assembling of the formula by "gap of Sunday vs Monday" and
the rearranging of the formula.
    (a) He uses a date of "January 3", not "January 4".
    (b) When he rounds the days in total in the week number,
        he uses a peculiar value of "5".

I assemble the formula that I arranged here to calculate the week number of "Starting
on Monday" using the weekday number of "Starting on Monday" from a beginning.
Therefore the indication mentioned above is improved.
    (a) I use a date of "January 4" according to words of specifications.
    (b) When I round the days in total to the week number, I use "6".

A meaning and the result of the formula do not change, but I think that it becomes
somewhat more plain than the formula of Frank Kabel.



[ Link to here ] [ News of the site move ]

--- Explanation of the formula developed by barry houdini ---  ( addition : 10 July 2015)
    ( It first appeared in    16 May 2006    MrExcel.com / Year-Week Number. )

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

  1     2     3     4     5     6     7    ... Number of weekday: WEEKDAY( ,2)
[Mon] [Tue] [Wed] [Thu] [Fri] [Sat] [Sun]
 1/1    2     3    (4)    5     6     7    ... 1st week of new year(Jan.)
12/31 1/1     2     3    (4)    5     6    ... 1st week of new year(Jan.)
12/30  31   1/1     2     3    (4)    5    ... 1st week of new year(Jan.)
12/29  30    31   1/1     2     3    (4)   ... 1st week of new year(Jan.)
12/28  29    30    31   1/1     2     3    ... Last week of previous year(Dec.)
12/27  28    29    30    31   1/1     2    ... Last week of previous year(Dec.)
12/26  27    28    29    30    31   1/1    ... Last week of previous year(Dec.)

(a) Target date : A1

(b) End of last week of the Target date (Sunday)
        A1 - WEEKDAY(A1, 2)

(c) Thursday of the Target date's week
        A1 - WEEKDAY(A1, 2) + 4

(d) Year of "Thursday of the Target date's week"
        YEAR(A1 - WEEKDAY(A1, 2) + 4)

(e) End of the Target date's week (Sunday)
       A1 - WEEKDAY(A1, 2) + 7

(f) Jan. 4th in the Year of "Thursday of the Target date's week"
        DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4)

(g) "End of last week (Sunday)" of
              "Jan. 4th in the Year of Thursday of the Target date's week"
        DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4)
            - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2)

(h) Formula of calculation is [ (e) - (g) ].
      It is from Sunday to Sunday.
      That means, the result is a multiple of 7.
      When divide it by 7; the value is 1, 2, 3 ....  (Integer (there is no decimal))

(i) [ (e) - (g) ]  is
        (A1 - WEEKDAY(A1, 2) + 7)
            - ( DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4)
                - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) )
 
(j) A1 - WEEKDAY(A1, 2)
      + 7
      - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4)
      + WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2)
 
(k) -- Divide it by 7 and Put "+7" outside a parenthesis --
      ( A1 - WEEKDAY(A1, 2)
            - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4)
            + WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2)
      ) / 7 + 1
 
(l) ( A1 - WEEKDAY(A1, 2) - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4) ) / 7
        + ( WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7 + 1
 
(m) regard "(A1 - WEEKDAY(A1, 2) - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4))" as Alpha.

     (Alpha / 7) + (WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7 + 1
 
(n) INT(Alpha / 7) + Mod(Alpha , 7) / 7
        + (WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7 + 1
 
(o) INT(Alpha / 7)
      + ( Mod(Alpha ,7) + WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7
          + 1

(p) The result of the formula is an integer to show it in (h).
      Therefore, the 2nd line part is an integer, too.
      It always becomes 7 when I really calculate in the 2nd line parenthesis.
      In other words, the 2nd line always becomes 1.

(q) Mod(Alpha ,7)
        is  Mod( (A1 - WEEKDAY(A1, 2)) - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4) , 7)

      In other words ... Mod( "Some Sunday" - "Jan. 4th Some year" , 7)

      1     2     3     4     5     6     7  ... Number of weekday: WEEKDAY( ,2)
    [Mon] [Tue] [Wed] [Thu] [Fri] [Sat] [Sun]
    (Some Sunday - Monday    (of Jan. 4th Some year)) is "7x + 6" days.
    (Some Sunday - Tuesday   (of Jan. 4th Some year)) is "7x + 5".
    (Some Sunday - Wednesday (of Jan. 4th Some year)) is "7x + 4".
    (Some Sunday - Thursday  (of Jan. 4th Some year)) is "7x + 3".
    (Some Sunday - Friday    (of Jan. 4th Some year)) is "7x + 2".
    (Some Sunday - Saturday  (of Jan. 4th Some year)) is "7x + 1".
    (Some Sunday - Sunday    (of Jan. 4th Some year)) is "7x + 0".

      "6 to 0"  is  ( 7 - WEEKDAY("Jan. 4th Some year" , 2) ) .
                      ( 7 - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) .

      Therefore ...
          Mod( Alpha , 7 )
              Mod( (A1 - WEEKDAY(A1, 2)) - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4) , 7 )
                Mod( ( 7x + (7 - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2)) ), 7 )
                  0 + (7 - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2))
                    7 - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2)

(r) Therefore, the 2nd line part of the formula (o) is  ...
      ( Mod(Alpha ,7) + WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7
           ( (7 - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2))
                          + WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7
                 ( 7 ) / 7
                   1 .

(s) Formlua (o)  is  INT(Alpha / 7) + (7 / 7) + 1
         INT((A1 - WEEKDAY(A1, 2) - DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4)) / 7) + 1 + 1

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



[ Link to here ]

--- Support states by Calc in OpenOffice.org/Apache OpenOffice/Libre Office ---

      (a) WEEKNUM function
             It is the week number that is completely based on ISO8601.
             ("Starting on Monday" and "Starting on Sunday" are possible.)

      (b) WEEKNUM_ADD function
             It is compatible with WEEKNUM function of Excel.

      (c) DatePart function of Basic
             It is the week number that is completely based on ISO8601.
             ("Starting on Monday" and "Starting on Sunday" are possible.)

      (d) Format function of Basic
             It is not listed in a help, but "ww" (format symbol of week number) works.
             However, 3rd argument(firstdayofweek) and 4th argument(firstweekofyear)
             are not implemented. The calculation result is the same as follows in VBA.
                  Format(date, "ww", vbSunday, vbFirstJan1)



[ AddinBox Home (Japanese) ]  [ English Home ]  


AddinBox ( K.Tsunoda in Japan ) CopyRight(C) 2014 Allrights Reserved.