Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 4th 05, 10:31 PM
jPeich
 
Posts: n/a
Default 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.

  #2   Report Post  
Old January 4th 05, 10:58 PM
Ron de Bruin
 
Posts: n/a
Default

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.



  #3   Report Post  
Old January 4th 05, 11:29 PM
Frank Kabel
 
Posts: n/a
Default

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.





  #4   Report Post  
Old January 4th 05, 11:37 PM
Ron de Bruin
 
Posts: n/a
Default

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.







  #5   Report Post  
Old January 5th 05, 02:21 AM
Ron Rosenfeld
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to get week number in month in excel ? ikin Charts and Charting in Excel 2 January 16th 05 06:54 PM
Conditional Formatting (Date vs Number) [email protected] Excel Discussion (Misc queries) 7 December 20th 04 11:23 PM
How do you change the date to a Number help me Excel Discussion (Misc queries) 8 December 15th 04 08:54 AM
Use Julian Date To Create Serial Number antho10359 Excel Discussion (Misc queries) 4 December 9th 04 02:50 AM
How can I create formula that turns a date into the week # in don Excel Discussion (Misc queries) 0 November 28th 04 10:21 PM


All times are GMT +1. The time now is 11:38 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017