Getting the Kalendar Week within VBA

[KUS]

Hi all,

within an actual project i need VBA to deliver the calendar week,
corresponding to a date.

If there´s a worksheet function able to do the job this will do instead,
naturally.

Thanks a lot in advance!

 

[Nigel]

Use the standard Excel function WEEKNUM perhaps?

 

[Mike H]

Hi,

Requires analysis toolpak
=WEEKNUM(A1)

without analysis toolpak
=INT((A1-DATE(YEAR(A1),1,1))/7)+1


ISO week mumber
=INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2


or in VB
wk = DatePart("ww", "8/11/2008")

Mike

 

[KUS]

...thanks a lot for your speedy and most useful hints!

Since the toolpak configuration on the clients´machines is not unified (and
undocumented),
i will try the non-poolpak-VBSolution (works greatly fast).

Yours sincerely,

Kai
Colgone, Germany

 

[ShaneDevenshire]

Hi,

One other consideration may be that to use the Analysis ToolPak functions in
the VBE you need to attach the Analysis ToolPak - VBA. And then you need to
reference it - in the VBE choose Tools, References, and put a check beside
atpvbaen.xls.

 

[Harald Staff]

Kai

Beware!!! Germany, like most of Europe, uses the ISO week system. When
January 1st is on a friday, saturday or sunday those systems are one week
different.

So if you use WEEKNUM or similar now, it will look pretty fine until 2010,
when it starts returning a wrong value and nobody understands why it
suddenly fails.

See http://www.rondebruin.nl/weeknumber.htm for more on this.

HTH. Best wishes Harald