On Tue, 2 Nov 2004 09:05:01 -0800, "soren"
wrote:
I am working with week numbers and need Excel to accept as week 1 the first
week of the year with 4 or more days. I.e. week starting Monday 3rd January
2005 should be reflected as week 1.
The program reflects this week as week 2 by default. How to change this?
Here is a UDF that will compute the 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, <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the above code into the window that opens.
To use it, in some cell enter =ISOWeeknum(cell_ref) where cell_ref contains a
date.
--ron
|