Weeknum

[Guest]

I work in a Swedish environment, and would like to use the function Weeknum,
the thing is, that Sweden uses a different way to number our weeks, for
example, the date 2005-01-01, is weeknumber 53, but if I use the function
Weeknum, it computes to weeknum 1. Is there anyone who has any suggestions of
how to tackle that problem?

MickeW

 

[Bob Phillips]

Sounds like ISO week number. Take a look at
http://www.cpearson.com/excel/weeknum.htm

 

[Ron Rosenfeld]

I work in a Swedish environment, and would like to use the function Weeknum,
the thing is, that Sweden uses a different way to number our weeks, for
example, the date 2005-01-01, is weeknumber 53, but if I use the function
Weeknum, it computes to weeknum 1. Is there anyone who has any suggestions of
how to tackle that problem?

MickeW

You can use this UDF:

=======================
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 use this, <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 opens.

To use this UDF, enter =ISOWeeknum(A1) into some cell. Substitute for A1 your
cell containing the date to be converted.


--ron

 

[Andy Wiggins]

The example here will suit your requirement:

http://www.bygsoftware.com/Excel/functions/iso_dates.htm

 

[Ron de Bruin]

You can find more info about ISO here
http://www.rondebruin.nl/isodate.htm

 

[Bob Phillips]

Sorry Ron, I forget that one. Will note it for future use ;-)

Bob

 

[Ron de Bruin]

Hi Bob

No problem.

Together with Chip's page you can find everything about ISO.
(The link to Chip's site is also on this page)

 

[Guest]

I had the same problem, (also Sweden) i found that the easiest way to get
around the problem is:

Format("my date";"vv";2;2)

Now 05-01-01 is week 53 and 05-01-02 number 1.


"MickeW" skrev:

 

[Bob Phillips]

Maybe you want ISO week number. See http://www.rondebruin.nl/isodate.htm

 

[hhalle]

I wrote my own udf called WkNr because of the incorrect weeknumbe
handling:

If you call the function in an empty cell and enter the date of anothe
cell
the function returns the weeknumber.
Works is Holland and week 53, when applicable is shown.
Weekday() is an existing vba function in Excel
I have not included any remarks but I think it explains itself.
The rule applied for calculating the first week of any particular yea
is the following:
If january 1 falls on a Thursday or later week 1 is the nex monday
otherwise the previous monday is week 1

example
COLA COLB
ROW1 01/01/2005 52 the formula is wknr(A1)
ROW2 01/01/2004 53 the formula is wknr(A2)
ROW3 01/01/2006 52 the formula is wknr(A3)





Public Function WkNr(wdate As Date) As Integer
Dim wk As Integer
Dim wd As Integer
Dim yr As Integer
Dim tDate, ttdate As Date
yr = Year(wdate)
tDate = DateValue("01-01-" & yr)
ttdate = tDate
wd = Weekday(tDate)
Select Case wd
Case 1
tDate = tDate + 1
Case 2
tDate = tDate + 0
Case 3
tDate = tDate - 1
Case 4
tDate = tDate - 2
Case 5
tDate = tDate + 4
Case 6
tDate = tDate + 3
Case 7
tDate = tDate + 2
End Select
If Int((wdate - tDate) / 7) + 1 > 0 Then
If ((wdate = DateValue("30-12-" & yr) And Int((wdate - tDate)
7) + 1 = 53 And (Weekday(DateValue("30-12-" & yr)) = 2)) Or (wdate
DateValue("31-12-" & yr) And Int((wdate - tDate) / 7) + 1 = 53 An
(Weekday(DateValue("31-12-" & yr)) = 2 Or Weekday(DateValue("31-12-"
yr)) = 3))) Then
WkNr = 1
Else
WkNr = Int((wdate - tDate) / 7) + 1
End If
Else
WkNr = WkNr(wdate - Day(wdate))
End If
End Function

Good luck,

Hans (hhalle) :