How to mimic the "Weeknum" function of Excel in the Script? :

--
Baap
-----------------------------------------------------------------------
Baapi's Profile: http://www.excelforum.com/member.php...fo&userid=2733
View this thread: http://www.excelforum.com/showthread.php?threadid=46836

Hi Baapi

See
http://www.rondebruin.nl/weeknumber.htm

This part
http://www.rondebruin.nl/weeknumber.htm#information
On Chip's site you find one for the Excel Weeknum


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Baapi" wrote in message
...

How to mimic the "Weeknum" function of Excel in the Script? :)


--
Baapi
------------------------------------------------------------------------
Baapi's Profile: http://www.excelforum.com/member.php...o&userid=27333
View this thread: http://www.excelforum.com/showthread...hreadid=468369

I have a very limited access to internet and the site you have referre
to is blocked under "Free Wares" category.

Anyway, in the mean while I thought of the following logic. But, I'
not sure if this is the perfect one and will work under al
conditions.

(DateSerial (Year(X), Month(X), Day(X) ) - DateSerial (Year(X), 1, 0
)/ 7

Comments? Suggestions

--
Baap
-----------------------------------------------------------------------
Baapi's Profile: http://www.excelforum.com/member.php...fo&userid=2733
View this thread: http://www.excelforum.com/showthread.php?threadid=46836

On Fri, 16 Sep 2005 16:14:42 -0500, Baapi
wrote:


How to mimic the "Weeknum" function of Excel in the Script? :)

This comes close:

===============
Function VBWeeknum(dt As Date, _
Optional FirstDayOfWeek As Integer = 1) As Integer

VBWeeknum = DatePart("ww", dt, FirstDayOfWeek, vbFirstJan1)
End Function
================

One difference is that the worksheet function WEEKNUM only accepts Sunday or
Monday as being the first day of the week, whereas this UDF can accept any day
as being day 1.

So if you wanted to truly mimic the Weeknum function, you might check that
FirstDayOfWeek is 1 or 2, and if not, return a #NUM! error.


--ron

On Fri, 16 Sep 2005 22:15:27 -0400, Ron Rosenfeld
wrote:

On Fri, 16 Sep 2005 16:14:42 -0500, Baapi
wrote:


How to mimic the "Weeknum" function of Excel in the Script? :)

This comes close:

===============
Function VBWeeknum(dt As Date, _
Optional FirstDayOfWeek As Integer = 1) As Integer

VBWeeknum = DatePart("ww", dt, FirstDayOfWeek, vbFirstJan1)
End Function
================

One difference is that the worksheet function WEEKNUM only accepts Sunday or
Monday as being the first day of the week, whereas this UDF can accept any day
as being day 1.

So if you wanted to truly mimic the Weeknum function, you might check that
FirstDayOfWeek is 1 or 2, and if not, return a #NUM! error.


--ron

Thanks to Norman Jones, we can now do a better job of mimicking the WEEKNUM
function, including errors for invalid entries:

===================================
Function VBWeeknum(dt As Date, _
Optional FirstDayOfWeek As Integer = 1)

Select Case FirstDayOfWeek
Case 1 To 2
VBWeeknum = DatePart("ww", dt, FirstDayOfWeek, vbFirstJan1)
Case Else
VBWeeknum = CVErr(xlErrNum)
End Select
End Function
==================================


--ron

On Fri, 16 Sep 2005 18:17:53 -0500, Baapi
wrote:


I have a very limited access to internet and the site you have referred
to is blocked under "Free Wares" category.

Anyway, in the mean while I thought of the following logic. But, I'm
not sure if this is the perfect one and will work under all
conditions.

(DateSerial (Year(X), Month(X), Day(X) ) - DateSerial (Year(X), 1, 0 )
)/ 7

Comments? Suggestions?

Your formula will give different results than Excel's WEEKNUM function unless
the year starts on a Sunday.

See my other responses for some other ideas.


--ron