Defining week number

  • Thread starter Thread starter gilbert
  • Start date Start date
G

gilbert

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

Please advise.

Thank you.

Rgds,
Gilber
 
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
 
Hi Gilbert!

First define your weeknumbering system.

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Where can I obtain Analysis Tool Pack? Is it a free add-ins? What happe
if I created a workbook using WeekNum function but the user may no
have this add-ins, can they see the same results?

If not, is there any other better alternative to do that
 
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top