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

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

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

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

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

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

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