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

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

--
HTH

Bob Phillips

"MickeW" <MickeW@discussions.microsoft.com> wrote in message
news:D9D02C1E-81F4-4D13-90C4-3D55C8B6F47F@microsoft.com...
> 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 find more info about ISO here
http://www.rondebruin.nl/isodate.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message news:OOj1Oj8eFHA.2420@TK2MSFTNGP12.phx.gbl...
> Sounds like ISO week number. Take a look at
> http://www.cpearson.com/excel/weeknum.htm
>
> --
> HTH
>
> Bob Phillips
>
> "MickeW" <MickeW@discussions.microsoft.com> wrote in message
> news:D9D02C1E-81F4-4D13-90C4-3D55C8B6F47F@microsoft.com...
>> 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
>
>

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

Bob

"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:e0nS2y$eFHA.2520@TK2MSFTNGP09.phx.gbl...
> You can find more info about ISO here
> http://www.rondebruin.nl/isodate.htm
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:OOj1Oj8eFHA.2420@TK2MSFTNGP12.phx.gbl...
> > Sounds like ISO week number. Take a look at
> > http://www.cpearson.com/excel/weeknum.htm
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "MickeW" <MickeW@discussions.microsoft.com> wrote in message
> > news:D9D02C1E-81F4-4D13-90C4-3D55C8B6F47F@microsoft.com...
> >> 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
> >
> >
>
>

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" <phillips@tiscali.co.uk> wrote in message news:%23cC72vIfFHA.2152@TK2MSFTNGP14.phx.gbl...
> Sorry Ron, I forget that one. Will note it for future use ;-)
>
> Bob
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> news:e0nS2y$eFHA.2520@TK2MSFTNGP09.phx.gbl...
>> You can find more info about ISO here
>> http://www.rondebruin.nl/isodate.htm
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:OOj1Oj8eFHA.2420@TK2MSFTNGP12.phx.gbl...
>> > Sounds like ISO week number. Take a look at
>> > http://www.cpearson.com/excel/weeknum.htm
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "MickeW" <MickeW@discussions.microsoft.com> wrote in message
>> > news:D9D02C1E-81F4-4D13-90C4-3D55C8B6F47F@microsoft.com...
>> >> 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
>> >
>> >
>>
>>
>
>

On Tue, 28 Jun 2005 02:24:03 -0700, MickeW <MickeW@discussions.microsoft.com>
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

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" <MickeW@discussions.microsoft.com> wrote in message
news:D9D02C1E-81F4-4D13-90C4-3D55C8B6F47F@microsoft.com...
> 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

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

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

--
HTH

Bob Phillips

"Facet" <Facet@discussions.microsoft.com> wrote in message
news:F4866B88-0D47-42F2-ACCE-E28A6D782362@microsoft.com...
> 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

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)