Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Geocyclist
 
Posts: n/a
Default 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!
  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #3   Report Post  
Geocyclist
 
Posts: n/a
Default

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

  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #5   Report Post  
Geocyclist
 
Posts: n/a
Default

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



  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #7   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #8   Report Post  
Geocyclist
 
Posts: n/a
Default



"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
  #9   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #10   Report Post  
Daniel.M
 
Posts: n/a
Default

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.




  #11   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
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
Will the Excel WEEKNUM function become ISO 8601 compliant? Gilles Moerdijk Excel Worksheet Functions 3 February 8th 05 08:05 PM
Excel formula similar to a loop in Basic? Cashtime Excel Worksheet Functions 2 February 6th 05 08:53 PM
Opening Office Excel file in Works suite DON Excel Discussion (Misc queries) 1 January 30th 05 06:59 AM
Recognizing weekends and changing to weekdays in Excel hanauer Excel Discussion (Misc queries) 2 January 8th 05 03:13 AM
Excel 2003 - Formula result shows as 0:00 Serena Excel Worksheet Functions 4 November 11th 04 11:18 PM


All times are GMT +1. The time now is 10:59 AM.

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"