Getting the Kalendar Week within VBA

K

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!
 
R

royUK

Take a look at the WEEKNUM Function, I can't remember if it is part o
the Analysis ToolPak which needs to be added from your Office CD. Thes
Functions are now built in in Excel 200

=WEEKNUM(A1) where the date is in A

=WEEKNUM(TODAY()

--
royU

Hope that helps.

RoyU
 
M

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
 
K

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
 
S

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.
 
H

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
 
Top