first week of year - according to european standard
 

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?

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


"soren" wrote in message
...
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?

On Tue, 2 Nov 2004 09:05:01 -0800, "soren"
wrote:

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

Hi Soren

See also
http://www.erlandsendata.no/english/...dateweeknumber
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

"soren" skrev i melding
...
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?