Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default DateDiff problem

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default DateDiff problem

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

.

.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default DateDiff problem

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default DateDiff problem

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
.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Colon at the end of excel file name(ex: problem.xls:1, problem.xls financeguy New Users to Excel 2 January 15th 10 02:15 AM
"DATEDIFF" LOCATION IN EXCEL 2007 dickvw Excel Worksheet Functions 1 December 14th 07 07:48 PM
Started out as an Access problem. Now an Excel problem RobertM Excel Discussion (Misc queries) 2 April 26th 06 07:30 PM
Where is DateDiff function in Excel 2002 ? Nigel Welch Excel Worksheet Functions 4 March 4th 05 04:18 PM
problem with a conditional max problem Brian Cornejo Excel Discussion (Misc queries) 1 February 18th 05 07:25 PM


All times are GMT +1. The time now is 04:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"