Getting the Kalendar Week within VBA
 

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!

--
Kai
Cologne, Germany

Getting the Kalendar Week within VBA
 

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

=WEEKNUM(A1) where the date is in A1

=WEEKNUM(TODAY())


--
royUK

Hope that helps.

RoyUK
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26892

Getting the Kalendar Week within VBA
 

Use the standard Excel function WEEKNUM perhaps?

--

Regards,
Nigel




"KUS" wrote in message
...
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!

--
Kai
Cologne, Germany

Getting the Kalendar Week within VBA
 

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" wrote:

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!

--
Kai
Cologne, Germany

Getting the Kalendar Week within VBA
 

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



"Mike H" wrote in message
...
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" wrote:

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!

--
Kai
Cologne, Germany

Getting the Kalendar Week within VBA
 

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.

--
Thanks,
Shane Devenshire


"KUS" wrote:

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!

--
Kai
Cologne, Germany

Getting the Kalendar Week within VBA
 

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


"KUS" wrote in message
...

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



"Mike H" wrote in message
...
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" wrote:

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!

--
Kai
Cologne, Germany