I need week number in excell from a date, first week must be mini.
 

I need to get the week number in excell from a cell with a date (dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose the
first week

Thanks.

Hi jPeich

See Chip Pearson's site
http://www.rondebruin.nl/weeknumber.htm

And also this week calendar file
http://www.rondebruin.nl/weeknumber.htm



Regards Ron de Bruin
http://www.rondebruin.nl



"jPeich" wrote in message
...
I need to get the week number in excell from a cell with a date
(dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the
first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose the
first week

Thanks.

Hi Ron
I think for the first one you meant:
http://www.cpearson.com/excel/weeknum.htm

:-))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ron de Bruin" schrieb im Newsbeitrag
...
Hi jPeich

See Chip Pearson's site
http://www.rondebruin.nl/weeknumber.htm

And also this week calendar file
http://www.rondebruin.nl/weeknumber.htm



Regards Ron de Bruin
http://www.rondebruin.nl



"jPeich" wrote in message
...
I need to get the week number in excell from a cell with a date
(dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with
day
nº 1, first week with at least 4 days of the new year or finally the
first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose
the
first week

Thanks.

Thanks Frank

My Ctrl-C is not working very good anymore
I will purchase a new keyboard soon

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Frank Kabel" wrote in message
...
Hi Ron
I think for the first one you meant:
http://www.cpearson.com/excel/weeknum.htm

:-))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ron de Bruin" schrieb im Newsbeitrag
...
Hi jPeich

See Chip Pearson's site
http://www.rondebruin.nl/weeknumber.htm

And also this week calendar file
http://www.rondebruin.nl/weeknumber.htm



Regards Ron de Bruin
http://www.rondebruin.nl



"jPeich" wrote in message
...
I need to get the week number in excell from a cell with a date
(dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with
day
nº 1, first week with at least 4 days of the new year or finally the
first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose
the
first week

Thanks.

On Tue, 4 Jan 2005 13:31:03 -0800, jPeich
wrote:

I need to get the week number in excell from a cell with a date (dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose the
first week

Thanks.

This can be done in VBA.

Do you want to be able to select one of your three possibilities?

Or do you just want an output that conforms to the ISO standard?

If the latter, then this UDF will do that:

=====================
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
=====================

If the former, then this UDF can be modified, and optional arguments added to
denote the Type of weeknumber you wish; and also the Starting date of the week.
The DatePart VBA function is very flexible in this regard.

To enter the above UDF, <alt<F11 opens the VB 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 this UDF, merely insert =ISOWeeknum(dt) into some cell where "dt" is
either an Excel date or a reference to a cell that contains a date.


--ron