Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Will the Excel WEEKNUM function become ISO 8601 compliant? | Excel Worksheet Functions | |||
Excel formula similar to a loop in Basic? | Excel Worksheet Functions | |||
Opening Office Excel file in Works suite | Excel Discussion (Misc queries) | |||
Recognizing weekends and changing to weekdays in Excel | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |