WeekNum ISO

[RPitoyo]

WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start from
January 3. The first week should contain the first Thursday in that year and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.

 

[Frank Kabel]

Hi
see:
http://cpearson.com/excel/weeknum.htm
an
http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/trackback/

 

[Arvi Laanemets]

I myself use this one:
=IF(INT((A1-DATE(YEAR(A1);1;1)+1+7-WEEKDAY(A1;2)+4)/7)=0;INT(((DATE(YEAR(A1)
-1;12;31)-DATE(YEAR(A1)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(A1)-1;12;31);2)+4)/7);
IF(AND(INT((A1-DATE(YEAR(A1);1;1)+1+7-WEEKDAY(A1;2)+4)/7)=53;WEEKDAY(DATE(YE
AR(A1);12;31
);2)<4);1;INT((A1-DATE(YEAR(A1);1;1) +1+7-WEEKDAY(A1;2)+4)/7)))

 

[Daniel.M]

Hi Arvi,

With a date in A1:

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

or
=1+INT(MIN(MOD(A1-DATE(YEAR(A1)+{-1;0;1},1,5)+
WEEKDAY(DATE(YEAR(A1)+{-1;0;1},1,3)),734))/7)

or (calendar 1900 specific)
=INT(MOD(INT((A1-2)/7)+0.6,52+5/28))+1

Regards,

Daniel M.

 

[Ron Rosenfeld]

WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start from
January 3. The first week should contain the first Thursday in that year and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.

You can use this UDF:

===============
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
=================

To enter it, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code into the window that opens.

To use it:

=ISOWeeknum(date) will give the ISO compliant weeknumber for any date. 'date'
may be a cell reference.


--ron