DateDiff problem

[Antje Crawford]

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

 

[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

 

[Ron Rosenfeld]

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

 

[Antje Crawford]

Ron,
thanks a lot for the help and clarification.

Antje

-----Original Message-----