Convert date into week number

  • Thread starter Thread starter Frank van Thiel
  • Start date Start date
F

Frank van Thiel

I have a list of dates (for example 02-05-2004) and would
like to have to weeknumbers which belong to that dat, is
this possible in Ecel?
 
If you have the Analysis Toolpak (ATP)* installed and enabled,
perhaps 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 in WEEKNUM, e.g.: "2" in B1 above,
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.
 
Hi Frank!

In any week number problem of whatever nature it is crucial to
establish your definitions of:

· What day of the week a week starts on, and
· How is the first day of the first week determined.

For a discussion on Week numbers generally see:

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

The major difficulty is alternative algorithms for determining the
first day of week one and for determining what day a week starts on.
Here are various systems and / requirements that all produce their own
solutions.

1. The Analysis ToolPak WEEKNUM function allows two bases. Both
provide for Jan-1 as being the first day of week number 1 with the two
alternatives providing that Sunday (Alternative 1) or Monday
(Alternative 2) being the first day of all subsequent weeks.
2. We can also use a simple formula for calculating week numbers
where Jan-1 is defined as the first day of week one and all subsequent
weeks starting 7 days later.
3. There are formulas and functions for calculating week numbers
where the ISO8601:2000 algorithm is used. Monday is regarded as the
first day of the week with week 1 starting on the Monday of the week
containing Jan-4.
4. Financial Year based systems where the financial year might
start on a given date.
5. Special requirements such as fixed weekly cycles (e.g. 4
weeks, 13 weeks etc). Such requirements need to specify the base date
and what day the week starts.
6. Term / Semester week numbering. Here we have "gross" systems
that include mid-term / mid-semester breaks and "net" systems which
exclude those weeks.
7. Month week numbering systems where the 1st of the month is
the first day of the first week and the fifth week is just an odd days
week.
8. Month week numbering systems where the first week starts on
the first Monday or Sunday of a Month and overlaps the subsequent
month.
9. Other bases limited only by the ingenuity of people who
devise systems (perhaps without considering whether the above
alternatives might be easier for all concerned).
 
I have a list of dates (for example 02-05-2004) and would
like to have to weeknumbers which belong to that dat, is
this possible in Ecel?
Click to expand...

It depends on what you mean by Weeknumber. There is the WEEKNUM function in
the Analysis Tool Pack. But this value will be inconsistent with the ISO
definition.

To obtain the ISO compatible week number, you can use this User Defined
Function:

=================================
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, then Insert/Module
and paste the code into the window that will open.

You can then use the function =ISOWeeknum(dt) as any other in that workbook.
dt can be a date or a cell reference containing a date.




--ron
 
Back
Top