|
|
|
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. : 10 Aug. 2014 ) (Jpn. & Eng. 2nd Edition : 10 July 2015 ) ( Porting to JavaScript : 13 Feb. 2021 ) |
--- ISO8601 Week Number Formula ----[ AddinBox Home (Japanese) ] [ English Home ]
( a ) Excel formula developed by Frank Kabel ( JavaScript code )
( b ) Excel formula developed by Barry Houdini ( JavaScript code )
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 ( dead link )
Microsoft Docs : 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 ]
--- Explanation of the formula developed by Frank Kabel ---
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. ( Original )
=INT((A1 - DATE(YEAR(A1 - WEEKDAY(A1 - 1) + 4), 1, 3)
+ WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1 - 1) + 4), 1, 3)) + 5) / 7)
Formula of Frank Kabel. (it has been processed to the weekday number of "Starting on Monday" , see above )
=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
=INT((A1 - [C] + [Dw] + 5 ) / 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)
[X] ： A1 - [E] The days to A1 in total
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))
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
=(A1 - WEEKDAY(A1, 2)) + 4
The year of [Jan. 4] is the year of Thursday in the week of A1.
[year]
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 ]
---- JavaScript code ( Frank Kabel )----
function ISO8601WeekNumber( ArgDate ) {
// ISO8601 week number formula (Excel) developed by Frank Kabel (Modified version)
// =INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4)
// +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2)+6)/7)
//
// Explanation of how the formula works.
// (Jpn) http://addinbox.sakura.ne.jp/Excel_Tips20.htm#Frank
// (Eng) http://addinbox.sakura.ne.jp/Excel_Tips20E.htm#Frank
var Week = (ArgDate.getDay() || 7); // Mon(1),Tue(2) --- Sat(6),Sun(7)
var Date1 = new Date(ArgDate.getTime());
Date1.setDate(Date1.getDate() - Week + 4);
var Date2 = new Date(Date1.getFullYear(), 0, 4); // Jan. 4
var Date2Week = (Date2.getDay() || 7); // Mon(1),Tue(2) --- Sat(6),Sun(7)
var DateDiff = ((ArgDate - Date2) / 86400000) + Date2Week;
return Math.floor((DateDiff + 6) / 7);
}
---- Enter the year in 4 digits and click the button. ----
[ Link to here ]
--- 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 (
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 (
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) Split "(Alpha / 7)" into "Integer part" and "Decimal part".
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. -- Verify with (q) & (r) below. --
(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) ) .
Therefore ...
Mod( Alpha , 7 )
(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
+ WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1, 2) + 4), 1, 4), 2) ) / 7
(s) Formlua (o) is INT(Alpha / 7) + (7 / 7) + 1
[ Link to here ]
---- JavaScript code ( Barry Houdini )----
function ISO8601WeekNumber( ArgDate ) {
// ISO8601 week number formula (Excel) developed by Barry Houdini
// =INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4))/7)+2
//
// Explanation of how the formula works.
// (Jpn) http://addinbox.sakura.ne.jp/Excel_Tips20.htm#barry
// (Eng) http://addinbox.sakura.ne.jp/Excel_Tips20E.htm#barry
var Week = (ArgDate.getDay() || 7); // Mon(1),Tue(2) --- Sat(6),Sun(7)
var Date1 = new Date(ArgDate.getTime());
var Date2 = new Date(ArgDate.getTime());
Date1.setDate(Date1.getDate() - Week);
Date2.setDate(Date2.getDate() - Week + 4);
var Date3 = new Date(Date2.getFullYear(), 0, 4); // Jan. 4
var DateDiff = (Date1 - Date3) / 86400000;
return Math.floor(DateDiff / 7) + 2;
}
---- Enter the year in 4 digits and click the button. ----
[ 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)
