formula to calculate the week number from a date?

  • Thread starter Thread starter luis
  • Start date Start date
Hi

How is a week defined by you. It's different for different countries. P.e.
here in Estonia week starts with Monday. And first week of year is the one,
where first Thuesday of year is falling in.

Arvi Laanemets
 
is there a formula or nice way without brute force?
TIA
luis
Click to expand...

Here is a User Defined Function to calculate the ISO weeknumber:

=========================
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 it, <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 appears.

To use it, enter =ISOWeeknum(dt) in a cell were dt is a reference to a cell
that contains the date in which you are interested.


--ron
 
Hi again

An example for calculating a weeknumber in format 'yyyy.ww' (I prefer this
format, because it enables sorting when there are data from severel years in
table) from date in A2 (I didn't edit delimiters in formulas, as there is
too much of them, and it'll be confusing when I miss one)

=(YEAR(A2)+AND(DayNum<4;WeekNum>50)*(-1)+AND(DayNum>262;WeekNum=1)*1) & "."
& TEXT(WeekNum;"00")

,where DayNum, WeekNum, Week1 and Week2 are named ranges, defined with cell,
you are the formula writing to, activated.

DayNum=DATEDIF(DATE(YEAR(A2);1;0);A2;"D")
Week1=INT(((DATE(YEAR(A2)-1;12;31) -
DATE(YEAR(A2)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(A2)-1;12;31);2)+4)/7)
Week2=IF(AND(INT((DayNum+7-WEEKDAY(A2;2)+4)/7)=53;WEEKDAY(DATE(YEAR(A2);12;3
1);2)<4);1;INT((DayNum+7-WEEKDAY(A2;2)+4)/7))
WeekNum=IF(INT((DayNum+7-WEEKDAY(A2;2)+4)/7)=0;Week1;Week2)

The formula above returns a string value. You can convert it into number,
enclosing it into VALUE function.
As I sayd before, the same date can belong to different week in different
countries (there are several ISO's). Probably you have to modify formulas
above.

There is a function for calculating a weeknumber in Excel2000, but there you
can determine the the starting day for week, but not how is determined the
first week of year (p.e. for me, the date 29. December 2003 belongs to week
2004.01, and date 02. January 2005 belongs to week 2004.53, but weeknum
returns 53 for first date and 1 for second one. And worse - p.e. for date
01. January 2004, which belongs to same week as 29. December 2003, it
returns 1!!!)
 
Back
Top