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

[Geocyclist]

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!

 

[Ron Rosenfeld]

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

 

[Geocyclist]

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]

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

 

[Geocyclist]

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]

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

 

[Ron Rosenfeld]

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

 

[Geocyclist]

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

 

[Ron Rosenfeld]

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

 

[Daniel.M]

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.

 

[Ron Rosenfeld]

Hi Ron,


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