Correct the function WEEKNUMBER

[Guest]

The function weeknumber does not work properly. When typing Jan 1 2005 it
should display 53 and not 1, when i typ jan 3rd (in colum 1) and
=weeknumber(a2) in collum b it should display 1 and not 2. because the weeks
in 2004 is 53 and not 52, is this my mistake or is this with excel that only
counts up to week 52.
I hope you can change this.

 

[Dave Peterson]

I think you should read Chip Pearson's notes on =weeknum()

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

As well as excel's help for the =weeknum() function.

 

[Ron de Bruin]

Hi Eric

You can use the ISO week function

Public Function IsoWeekNum(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
IsoWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

This in a cell
=IsoWeekNum(E1)

More info here
http://www.cpearson.com/excel/weeknum.htm

http://www.rondebruin.nl/calendar.htm

 

[Ron Rosenfeld]

On Sat, 6 Nov 2004 09:05:01 -0800, Eric Wessels <Eric

The function weeknumber does not work properly. When typing Jan 1 2005 it
should display 53 and not 1, when i typ jan 3rd (in colum 1) and
=weeknumber(a2) in collum b it should display 1 and not 2. because the weeks
in 2004 is 53 and not 52, is this my mistake or is this with excel that only
counts up to week 52.
I hope you can change this.

Perhaps what you are looking for is the ISO weeknumber standard. This is not
the same as the weeknum function in excel.

Here is a UDF that can compute 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 this UDF, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer, then Insert/Module
and paste the above code into the window that opens.

To use it: =isoweeknum(dt) where dt is either a date or a cell reference that
contains a date.


--ron

 

[Guest]

The hint is very usefull, however this should be a natural part of the Excel
formular portfolio.

 

[Myrna Larson]

Excel *has* a WEEKNUM function. It has limitations. If you want to learn about
them, check the link given. If not, use it as-is.

 

[Dave Peterson]

Wait until he asks about the =datedif() function.

<vbg>