Excel Formula WEEKNUM only works on weekdays 1 and 2, why?
 

Running Excel 2002 (10.6501.6626) updated to SP3 with Add-Ins Analysis
ToolPak enabled. The WEEKNUM formula will only work using weekday 1 and 2
(Week ending Saturday or Sunday). Review of HELP shows this formula should
work with days 1 to 7. I need to have my week calculated from Wednesday
through Tuesday but the WEEKNUM formula will not work. This is what I have
entered; =WEEKNUM(A1,4) where the formated date is in cell A1. The following
formula works just fine to sum the week ending Sunday night; =WEEKNUM(A1,2).
Any suggestions would be appreciated!

On Wed, 6 Apr 2005 18:53:09 -0700, "Geocyclist"
wrote:

Running Excel 2002 (10.6501.6626) updated to SP3 with Add-Ins Analysis
ToolPak enabled. The WEEKNUM formula will only work using weekday 1 and 2
(Week ending Saturday or Sunday). Review of HELP shows this formula should
work with days 1 to 7. I need to have my week calculated from Wednesday
through Tuesday but the WEEKNUM formula will not work. This is what I have
entered; =WEEKNUM(A1,4) where the formated date is in cell A1. The following
formula works just fine to sum the week ending Sunday night; =WEEKNUM(A1,2).
Any suggestions would be appreciated!

I think the HELP file is misleading. I believe the examples given of 1 or 2
are also the limits.

So far as suggestions, in your system, what are the weeknumbers for the dates:

31 DEC 2004
1 JAN 2005

??





--ron

Thanks for your reply. In my system the following weeknumbers display for the
given dates:

31 Dec 04 is week 53
1 Jan 05 is week 1

Regards,
Chris

"Ron Rosenfeld" wrote:

On Wed, 6 Apr 2005 18:53:09 -0700, "Geocyclist"
wrote:

Running Excel 2002 (10.6501.6626) updated to SP3 with Add-Ins Analysis
ToolPak enabled. The WEEKNUM formula will only work using weekday 1 and 2
(Week ending Saturday or Sunday). Review of HELP shows this formula should
work with days 1 to 7. I need to have my week calculated from Wednesday
through Tuesday but the WEEKNUM formula will not work. This is what I have
entered; =WEEKNUM(A1,4) where the formated date is in cell A1. The following
formula works just fine to sum the week ending Sunday night; =WEEKNUM(A1,2).
Any suggestions would be appreciated!

I think the HELP file is misleading. I believe the examples given of 1 or 2
are also the limits.

So far as suggestions, in your system, what are the weeknumbers for the dates:

31 DEC 2004
1 JAN 2005

??





--ron

On Wed, 6 Apr 2005 23:15:01 -0700, "Geocyclist"
wrote:

Thanks for your reply. In my system the following weeknumbers display for the
given dates:

31 Dec 04 is week 53
1 Jan 05 is week 1

Regards,
Chris


It's trivial to do that in Visual Basic as a User Defined function -- also the
code is easy to understand:

=========================
Function ChrisWeekNum(dt As Date) As Long
ChrisWeekNum = DatePart("ww", dt, vbWednesday, vbFirstJan1)
End Function
========================

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
above code into the window that opens.

To use this UDF, enter =ChrisWeekNum(dt) in some cell where dt is either an
actual Excel date, or a cell reference that contains a date (e.g. A1).


--ron

What can I say except "WOW"!!! I don't use VB that often and had no idea
about User Defined Functions. The function you wrote works great and solved
my problem. Thanks a million for your assistance!!!!

Best Regards,
Chris

"Ron Rosenfeld" wrote:

On Wed, 6 Apr 2005 23:15:01 -0700, "Geocyclist"
wrote:

Thanks for your reply. In my system the following weeknumbers display for the
given dates:

31 Dec 04 is week 53
1 Jan 05 is week 1

Regards,
Chris


It's trivial to do that in Visual Basic as a User Defined function -- also the
code is easy to understand:

=========================
Function ChrisWeekNum(dt As Date) As Long
ChrisWeekNum = DatePart("ww", dt, vbWednesday, vbFirstJan1)
End Function
========================

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
above code into the window that opens.

To use this UDF, enter =ChrisWeekNum(dt) in some cell where dt is either an
actual Excel date, or a cell reference that contains a date (e.g. A1).


--ron

On Thu, 7 Apr 2005 16:19:02 -0700, "Geocyclist"
wrote:

What can I say except "WOW"!!! I don't use VB that often and had no idea
about User Defined Functions. The function you wrote works great and solved
my problem. Thanks a million for your assistance!!!!

You're welcome. Glad to help and thank you for the feedback.


--ron

On Thu, 7 Apr 2005 16:19:02 -0700, "Geocyclist"
wrote:

What can I say except "WOW"!!! I don't use VB that often and had no idea
about User Defined Functions. The function you wrote works great and solved
my problem. Thanks a million for your assistance!!!!

Best Regards,
Chris

As I wrote, I liked the VB solution, but here's a worksheet formula solution
that should do the same thing:

=ROUNDUP((A1-DATE(YEAR(A1),1,8)+WEEKDAY(DATE(YEAR(A1),1,5)))/7+1,0)


--ron

"Ron Rosenfeld" wrote:

On Thu, 7 Apr 2005 16:19:02 -0700, "Geocyclist"
wrote:

What can I say except "WOW"!!! I don't use VB that often and had no idea
about User Defined Functions. The function you wrote works great and solved
my problem. Thanks a million for your assistance!!!!

Best Regards,
Chris

As I wrote, I liked the VB solution, but here's a worksheet formula solution
that should do the same thing:

=ROUNDUP((A1-DATE(YEAR(A1),1,8)+WEEKDAY(DATE(YEAR(A1),1,5)))/7+1,0)


--ron

Not a chance! You sold my on the VB solution. I use the Weeknum function in
a number of places on varoius SS within a workbook. Sort of using Excel where
a Data base would be more effective. Anyway, with the UDF I can change all my
Weeknum functions by just changing the VB module. This will be a big time
saver at the beginning of every project.

Many thanks for your assistance! I'd never heard of this form until I
located it a few days ago.

Regards,
Chris

On Sat, 9 Apr 2005 01:47:04 -0700, "Geocyclist"
wrote:

Not a chance! You sold my on the VB solution. I use the Weeknum function in
a number of places on varoius SS within a workbook. Sort of using Excel where
a Data base would be more effective. Anyway, with the UDF I can change all my
Weeknum functions by just changing the VB module. This will be a big time
saver at the beginning of every project.

Many thanks for your assistance! I'd never heard of this form until I
located it a few days ago.

You're very welcome.

I post the worksheet function solution, both as an exercise, and also because I
am told that there are some corporations that try to avoid VBA as a policy.


--ron

Hi Ron,

... but here's a worksheet formula solution that should do the same thing:
=ROUNDUP((A1-DATE(YEAR(A1),1,8)+WEEKDAY(DATE(YEAR(A1),1,5)))/7+1,0)

Also,

=1+INT((A1-DATE(YEAR(A1),1,2)+WEEKDAY(DATE(YEAR(A1),1,5)))/7)

Regards,

Daniel M.

On Sat, 9 Apr 2005 15:00:38 -0400, "Daniel.M"
wrote:

Hi Ron,

... but here's a worksheet formula solution that should do the same thing:
=ROUNDUP((A1-DATE(YEAR(A1),1,8)+WEEKDAY(DATE(YEAR(A1),1,5)))/7+1,0)

Also,

=1+INT((A1-DATE(YEAR(A1),1,2)+WEEKDAY(DATE(YEAR(A1),1,5)))/7)

Regards,

Daniel M.


Many ways to skin a cat in Excel.

Best,

--ron