first week of year - according to european standard

[soren]

I am working with week numbers and need Excel to accept as week 1 the first
week of the year with 4 or more days. I.e. week starting Monday 3rd January
2005 should be reflected as week 1.

The program reflects this week as week 2 by default. How to change this?

 

[Arvi Laanemets]

Hi

I use this formula (with date in cell G2, and NB! I left delimiters as I use
them - when they aren't passing, then edit the formula):

=IF(INT((G2 -
DATE(YEAR(G2);1;1)+1+7-WEEKDAY(G2;2)+4)/7)=0;INT(((DATE(YEAR(G2)-1;12;31)-DA
TE(YEAR(G2)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(G2)-1;12;31);2)+4)/7);IF(AND(INT((
G2-DATE(YEAR(G2);1;1)+1+7-WEEKDAY(G2;2)+4)/7)=53;WEEKDAY(DATE(YEAR(G2);12;31
);2)<4);1;INT((G2-DATE(YEAR(G2);1;1) +1+7-WEEKDAY(G2;2)+4)/7)))


Arvi Laanemets

 

[Ron Rosenfeld]

I am working with week numbers and need Excel to accept as week 1 the first
week of the year with 4 or more days. I.e. week starting Monday 3rd January
2005 should be reflected as week 1.

The program reflects this week as week 2 by default. How to change this?

Here is a UDF that will compute the weeknumber:

=================
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 this, <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the above code into the window that opens.

To use it, in some cell enter =ISOWeeknum(cell_ref) where cell_ref contains a
date.


--ron

 

[Harald Staff]

Hi Soren

See also
http://www.erlandsendata.no/english/index.php?d=enfunctionsdateweeknumber
and
http://www.cpearson.com/excel/weeknum.htm

If you want to play with this yourself, one good approach is that January
4th is always in week 1.

HTH. Best wishes Harald