Hello,
I am trying to create a custom function that tells me
which week of the year a certain date falls into.
I came up with the following:

Function WeekNumber(Weeks) As Integer
WeekNumber = DateDiff("ww", "1/1", Weeks)
End Function

For some reason, the result displayed is one week short,
e.g. if today's date is 7-Jul-03 then the above function
returns 27, although 7-Jul-03 falls into week 28.

The built-in Excel function Datedif does not seem to work
in VBA.

Any suggestions, solutions are greatly appreciated.
TIA.
BR, Antje Crawford

Jeff,
both add-ins you're mentioning are already checked in add-
in box but the weeknum function does not show up in the
drop-down list when typing in
application.worksheetfunction.weeknum
Am I understanding correctly that those add-ins need to be
checked in order for them to work?

BR, Antje

-----Original Message-----
If you use the addin Analysis ToolPak (ToolsAdd-Ins), it
has a spreadsheet function Weeknum that will return the
week number of the year. Their is also an addin for the
Analysis ToolPak - VBA that you can use with your below
code.
-----Original Message-----
Hello,
I am trying to create a custom function that tells me
which week of the year a certain date falls into.
I came up with the following:

Function WeekNumber(Weeks) As Integer
WeekNumber = DateDiff("ww", "1/1", Weeks)
End Function

For some reason, the result displayed is one week short,
e.g. if today's date is 7-Jul-03 then the above function
returns 27, although 7-Jul-03 falls into week 28.

The built-in Excel function Datedif does not seem to
work
in VBA.

Any suggestions, solutions are greatly appreciated.
TIA.
BR, Antje Crawford

.

.

On Tue, 8 Jul 2003 11:42:16 -0700, "Antje Crawford" wrote:

Jeff,
both add-ins you're mentioning are already checked in add-
in box but the weeknum function does not show up in the
drop-down list when typing in
application.worksheetfunction.weeknum
Am I understanding correctly that those add-ins need to be
checked in order for them to work?

BR, Antje

In the VB editor, Tools/References and ensure that atpvbaen.xls is checked.

The VBA ATP add-in needs to be checked as you seem to have done.

Then in your VBA module, all you need to do is type Weeknum(...) and the
variables should appear.

Weeknum is NOT a worksheetfunction; it is an Analysis ToolPak function.


--ron

Ron,
thanks a lot for the help and clarification.

Antje

-----Original Message-----
On Tue, 8 Jul 2003 11:42:16 -0700, "Antje Crawford"
wrote:

Jeff,
both add-ins you're mentioning are already checked in
add-
in box but the weeknum function does not show up in the
drop-down list when typing in
application.worksheetfunction.weeknum
Am I understanding correctly that those add-ins need to
be
checked in order for them to work?

BR, Antje

In the VB editor, Tools/References and ensure that
atpvbaen.xls is checked.

The VBA ATP add-in needs to be checked as you seem to
have done.

Then in your VBA module, all you need to do is type
Weeknum(...) and the
variables should appear.

Weeknum is NOT a worksheetfunction; it is an Analysis
ToolPak function.


--ron
.