Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Weeknum
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 |
#2
|
|||
|
|||
Sounds like ISO week number. Take a look at
http://www.cpearson.com/excel/weeknum.htm -- HTH Bob Phillips "MickeW" wrote in message ... 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 |
#3
|
|||
|
|||
On Tue, 28 Jun 2005 02:24:03 -0700, MickeW
wrote: 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 |
#4
|
|||
|
|||
The example here will suit your requirement:
http://www.bygsoftware.com/Excel/fun.../iso_dates.htm -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "MickeW" wrote in message ... 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 |
#5
|
|||
|
|||
You can find more info about ISO here
http://www.rondebruin.nl/isodate.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sounds like ISO week number. Take a look at http://www.cpearson.com/excel/weeknum.htm -- HTH Bob Phillips "MickeW" wrote in message ... 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 |
#6
|
|||
|
|||
Sorry Ron, I forget that one. Will note it for future use ;-)
Bob "Ron de Bruin" wrote in message ... You can find more info about ISO here http://www.rondebruin.nl/isodate.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sounds like ISO week number. Take a look at http://www.cpearson.com/excel/weeknum.htm -- HTH Bob Phillips "MickeW" wrote in message ... 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 |
#7
|
|||
|
|||
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) -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sorry Ron, I forget that one. Will note it for future use ;-) Bob "Ron de Bruin" wrote in message ... You can find more info about ISO here http://www.rondebruin.nl/isodate.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sounds like ISO week number. Take a look at http://www.cpearson.com/excel/weeknum.htm -- HTH Bob Phillips "MickeW" wrote in message ... 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 |
#8
|
|||
|
|||
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: 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 |
#9
|
|||
|
|||
Maybe you want ISO week number. See http://www.rondebruin.nl/isodate.htm
-- HTH Bob Phillips "Facet" wrote in message ... 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: 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 |
#10
|
|||
|
|||
I wrote my own udf called WkNr because of the incorrect weeknumber handling: If you call the function in an empty cell and enter the date of another 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 year 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 And (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) :) -- hhalle ------------------------------------------------------------------------ hhalle's Profile: http://www.excelforum.com/member.php...o&userid=20829 View this thread: http://www.excelforum.com/showthread...hreadid=382746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weeknum function says jan1=week1, mod to first 4 day week needed | Excel Discussion (Misc queries) | |||
weeknum function returns name error | Excel Worksheet Functions | |||
Will the Excel WEEKNUM function become ISO 8601 compliant? | Excel Worksheet Functions | |||
WeekNum ISO | Excel Worksheet Functions | |||
Weeknum | Excel Worksheet Functions |