DateDiff problem

A

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
 
A

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top