Defining week number

If you have the Analysis Toolpak installed,
you could try using WEEKNUM ?

For example, supposing you have dates in col A,
you could pickup the week # in col B by putting:

In B1: =weeknum(A1,2)
and copying down col B

The 2nd arg, i.e. "2", is a number that determines on what
day the week begins. "2" means the week begins on Monday.
The default is "1" - week begins on Sunday.

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
-----Original Message-----
"gilbert "

How can we let Excel automatically defined the production
week in a
particular year? Production week in this case is referred
to as week
ending whereby in a year, we will have 52 weeks. For
example, I need
Excel to return week number given a date.

Please advise.

Thank you.

Rgds,
Gilbert


---
Message posted from http://www.ExcelForum.com/

.

Hi Gilbert!

First define your weeknumbering system.

See:
Chip Pearson
http://www.cpearson.com/excel/weeknum.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"gilbert " wrote in message
...
How can we let Excel automatically defined the production week in a
particular year? Production week in this case is referred to as week
ending whereby in a year, we will have 52 weeks. For example, I need
Excel to return week number given a date.

Please advise.

Thank you.

Rgds,
Gilbert


---
Message posted from http://www.ExcelForum.com/

Hi
this add-in comes with MS Office included. Goto 'Tools - Add-in
manager' and check this Add-in. If it's not already installed Excel
prompts for your installation CDs

If a user does not have this add-in installed he only sees the #NAME
error

--
Regards
Frank Kabel
Frankfurt, Germany

"gilbert " schrieb im
Newsbeitrag ...
Where can I obtain Analysis Tool Pack? Is it a free add-ins? What
happen
if I created a workbook using WeekNum function but the user may not
have this add-ins, can they see the same results?

If not, is there any other better alternative to do that?


---
Message posted from http://www.ExcelForum.com/

Hi Gilbert!

Analysis ToolPak ships with all Versions of Excel.

Just use:

Tools Addins
Select Analysis ToolPak
OK

If you haven't used Analysis ToolPak before, you'll need the
installation CDROMs. If (for pre Excel XP versions) you don't see
Analysis ToolPak listed in the Addins dialog, you will need to insert
the CDROM and select the dialog appropriate to changing your
installation.

With Analysis ToolPak loaded, the Analysis ToolPak functions and all
dependent cells will return #NAME!

The four common Week numbering systems are below. Analysis ToolPak is
only needed for methods 2 and 3 and (pushed) it should be possible to
design more complex formulas that don't need the Analysis ToolPak
functions.

1. Week 1 starts 1-Jan and Week 2 starts on 8-Jan
Here, we use the formula:
=TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)

2. Week 1 starts 1-Jan and week 2 starts on the following Sunday
Here we use the Analysis ToolPak WEEKNUM function with second argument
of 1.
=WEEKNUM(A1,1)

3. Week 1 starts 1-Jan and week 2 starts on the following Monday
Here we use the Analysis ToolPak WEEKNUM function with second argument
of 2.
=WEEKNUM(A1,2)

4. The ISO 8601: 2000 Method. Week 1 starts on the Monday of the week
with the first Thursday of the calendar year. Week 2 starts the
following Monday. Here we use the formula:
=1+INT((A9-DATE(YEAR(A9+4-WEEKDAY(A9+6)),1,5)+WEEKDAY(DATE(YEAR(A9+4-W
EEKDAY(A9+6)),1,3)))/7)

The advantage of the ISO system is that all weeks, for all time have 7
days. With other methods you get odd weeks at the start and / or end
of the year. a disadvantage is that week 1 can start in late December
and week 53 can extend into January.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"gilbert " wrote in message
...
Where can I obtain Analysis Tool Pack? Is it a free add-ins? What
happen
if I created a workbook using WeekNum function but the user may not
have this add-ins, can they see the same results?

If not, is there any other better alternative to do that?


---
Message posted from http://www.ExcelForum.com/

Chip Pearson's page has the details at:

http://www.cpearson.com/excel/ATP.htm

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
--
"gilbert " wrote:

Where can I obtain Analysis Tool Pack? Is it a free add-
ins? What happen
if I created a workbook using WeekNum function but the
user may not
have this add-ins, can they see the same results?

If not, is there any other better alternative to do that?


---
Message posted from http://www.ExcelForum.com/

Hi Gilbert!

I found the following alternatives:

Both of these provided by Daniel Maher



=WEEKNUM(A1,1)

=1+INT((A1-DATE(YEAR(A1),1,2)+WEEKDAY(DATE(YEAR(A1),1,1)))/7)





=WEEKNUM(A1,2)

=1+INT((A1-DATE(YEAR(A1),1,2)+WEEKDAY(DATE(YEAR(A1),1,)))/7)




--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"gilbert " wrote in message
...
Where can I obtain Analysis Tool Pack? Is it a free add-ins? What
happen
if I created a workbook using WeekNum function but the user may not
have this add-ins, can they see the same results?

If not, is there any other better alternative to do that?


---
Message posted from http://www.ExcelForum.com/