Weeknum Function
 

Hi

I have two dates, in two columns. One is a start date, and one is a
completion date. What I would like to do is list all of the week numbers that
are included in the range between these two. So for example, if my start date
is 1 January 2005 and my completion date is 21 January 2005, I would like to
display 1, 2, 3.

Is this at all possible?

Many thanks to anyone who can help.

Anita,

Let me know if you've never worked with VB Editor before...

Open up the VB Editor (Alt+F11) and go to Tools,References and make sure there's a check next to atpvbaen.xls.

After that, paste in the Function below. Note: if you paste it into a module for the current workbook, the function will only be available in that workbook. If you paste it into a module in "Personal.xls" it will always be available.

Here's the Function:

'-------------------------
Function WeekNumList(Start_Date, End_Date)

Dim Results As String

Start_Weeknum = Weeknum(Start_Date)
End_Weeknum = Weeknum(End_Date)
Results = ""

For i = Start_Weeknum To End_Weeknum - 1
Results = Results & i & ", "
Next

Results = Results & End_Weeknum

WeekNumList = Results

End Function
'-------------------------

Then use the Function as you would any other:

=Weeknumlist(A1,B1)

Knightly

Hi

I don't have atpvbaen.xls as an option in my References, they all seem to be
Type Libraries. I pasted the function anyway, but it didn't work. I got the
error message: Sub or Function not defined.

Thanks

Anita

"sirknightly" wrote:


Anita,

Let me know if you've never worked with VB Editor before...

Open up the VB Editor (Alt+F11) and go to Tools,References and make
sure there's a check next to atpvbaen.xls.

After that, paste in the Function below. Note: if you paste it into a
module for the current workbook, the function will only be available in
that workbook. If you paste it into a module in "Personal.xls" it will
always be available.

Here's the Function:

'-------------------------
Function WeekNumList(Start_Date, End_Date)

Dim Results As String

Start_Weeknum = Weeknum(Start_Date)
End_Weeknum = Weeknum(End_Date)
Results = ""

For i = Start_Weeknum To End_Weeknum - 1
Results = Results & i & ", "
Next

Results = Results & End_Weeknum

WeekNumList = Results

End Function
'-------------------------

Then use the Function as you would any other:

=Weeknumlist(A1)

Knightly

AnitaML Wrote:
Hi

I have two dates, in two columns. One is a start date, and one is a
completion date. What I would like to do is list all of the week
numbers that
are included in the range between these two. So for example, if my
start date
is 1 January 2005 and my completion date is 21 January 2005, I would
like to
display 1, 2, 3.

Is this at all possible?

Many thanks to anyone who can help.


--
sirknightly

OK

I managed to find it and add it, but now it comes up with my favourite #VALUE!

Any ideas what I'm doing wrong?

Thanks

Anita

"AnitaML" wrote:

Hi

I don't have atpvbaen.xls as an option in my References, they all seem to be
Type Libraries. I pasted the function anyway, but it didn't work. I got the
error message: Sub or Function not defined.

Thanks

Anita

"sirknightly" wrote:


Anita,

Let me know if you've never worked with VB Editor before...

Open up the VB Editor (Alt+F11) and go to Tools,References and make
sure there's a check next to atpvbaen.xls.

After that, paste in the Function below. Note: if you paste it into a
module for the current workbook, the function will only be available in
that workbook. If you paste it into a module in "Personal.xls" it will
always be available.

Here's the Function:

'-------------------------
Function WeekNumList(Start_Date, End_Date)

Dim Results As String

Start_Weeknum = Weeknum(Start_Date)
End_Weeknum = Weeknum(End_Date)
Results = ""

For i = Start_Weeknum To End_Weeknum - 1
Results = Results & i & ", "
Next

Results = Results & End_Weeknum

WeekNumList = Results

End Function
'-------------------------

Then use the Function as you would any other:

=Weeknumlist(A1)

Knightly

AnitaML Wrote:
Hi

I have two dates, in two columns. One is a start date, and one is a
completion date. What I would like to do is list all of the week
numbers that
are included in the range between these two. So for example, if my
start date
is 1 January 2005 and my completion date is 21 January 2005, I would
like to
display 1, 2, 3.

Is this at all possible?

Many thanks to anyone who can help.


--
sirknightly