The late Frank Kabel was working on collecting formulas that would work without Analysis ToolPak. His replacement for the WEEKNUM function was a formula for ISO weeknumbers so strictly speaking it is not a substitute for the WEEKNUM function. Ron de Bruin and I have just updated the weeknumber page on Ron’s site.
ISO 8601:2000 Week Numbering Algorithm
[All weeks start on a Monday with Week 1 starting on Monday of the week with the first Thursday of the calendar year]
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)
Here are two formulas for week numbering using the two WEEKNUM argument options:
Replacing =WEEKNUM(B4,1)
[Week 1 starts on 1-Jan, Week 2 starts on the following Sunday]
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)
Replacing =WEEKNUM(B4,2)
[Week 1 starts on 1-Jan, Week 2 starts on the following Monday]
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,0))))/7)
Thanks to Daniel M for the much more the comments that produce a much more efficient approach to the WEEKNUM replacements than used by the original formulas. This should make our DataRefiner Addin a lot more efficient when used on large data tables.
To complete the “usual suspects” list:
Simple Week Numbering:
[Week 1 starts on 1-Jan, Week 2 starts 8-Jan, Week 53 has only 1 or (Leap Years) 2 days]
=INT((B4-DATE(YEAR(B4),1,0))/7)+1
The Analysis ToolPak WEEKNUM function algorithms suffer from having first and last weeks without 7 days and in both cases there can be years with Week 54. Simple week numbering starts on different days of the week and always has week 53 with one or two days. With ISO Week numbers week 1 may start as early as 29-Dec of the preceding calendar year but all weeks will have 7 days starting on a Monday; much better for payroll and statistical comparisons. Some calendar years will have 52 ISO weeks and some will have 53 ISO weeks. The ISO week numbering system is now being introduced in the European Union.
Ron de Bruin and I have constructed an Excel workbook week numbering Calendar creator using all four systems and this is available for download with unrestricted use from:
http://www.rondebruin.nl/weeknumber.htm
There are small amendments to the original version with fewer long formulas used.
For making this or any workbook independent of John Green’s ISOYEARSTART UDF you can use the following Excel “translation” of that UDF:
=DATE(YEAR(B4),1,1)-MOD(DATE(YEAR(B4),1,1)-2,7)+(7*(MOD(DATE(YEAR(B4),1,1)-2,7)>3))
Or, if A1 contains a four digit year number:
=DATE(A1,1,1)-MOD(DATE(A1,1,1)-2,7)+(7*(MOD(DATE(A1,1,1)-2,7)>3))
Without resorting to a text solution, no formula can correct the 1900 Leap Year error, although our Week Numbers Calendar does disguise the error.
Reliance on Analysis ToolPak is a major annoyance because the Addin may not be installed or checked. A default Excel installation has it unchecked. Also, there are international difficulties where you insert ATP formulas by VBA. Use of simpler User Defined Functions is not a robust solution when Security settings are Very High or High.
Until we synchronise the Earth’s rotation with the Earth’s Orbit around the Sun (Excel Version ?) we’ll have to live with the individual deficiencies of the four week numbering systems.
Just because I’ against CHOOSE(MOD(…)) formula construction ;-)
With a serialnum date in B4:
To replace =WEEKNUM(B4,1)
1st week > Jan 01
2nd week > Next Sunday
=1+INT((B4-(DATE(YEAR(B4),1,2)
-WEEKDAY(DATE(YEAR(B4),1,1))))/7)
To replace =WEEKNUM(B4,2)
1st week > Jan 01
2nd week > Next Monday
=1+INT((B4-(DATE(YEAR(B4),1,2)
-WEEKDAY(DATE(YEAR(B4),1,1))))/7)
Bye,
The second formula got screwed (don’t ask me why):
To replace =WEEKNUM(B4,2)
1st week ñ> Jan 01
2nd week ñ> Next Monday
=1+INT((B4-(DATE(YEAR(B4),1,2)
-WEEKDAY(DATE(YEAR(B4),1,0))))/7)
You can omit the 0 in the formula (default).
Thanks Daniel!
I’d only just emailed Ron saying that I thought you’d be able to improve on my hacked approach.
I’ll run it throgh our checking and amend the site.
We are building a data refining Addin and with large data tables, efficiency of formulas will be much more important than where you only have one or two cases.
Regards
Just to confirm, the two much improved formulas put forward by Daniel M above have been edited into the lead Blog.
Far easier to understand and much more efficient for use in our forthcoming Addin for refining data in Excel data tables. This will serve as a preliminary user friendly step in analysing data using, perhaps, our EasyFilter Addin.
And thanks to Dick for this Daily Dose of Excel for this Blog, which serves as a very useful place to set out consolidations of Excel related material.
Regards.
Hi Guys
What happens if you want to extract week numbers in a financial year for example 1st July is Week 1???
I am looking for a formula that will allow me to add a certain number of workdays from a start date. For example, if wed 10th of Oct 2007 is my start date in cell b2 and I want to add 9 workdays to this, it is not as simple as =9+b2 (resulting in 19 of Oct 2007) as this will not take into account the weekends.
Can anyone help me?
Cheers!
James
O.k… so I figured out the easy figuring of how to count workdays (with a simple “workday” function… sorry for taking up space for that question….
Here is a real question that I’ve been trying to figure out for quite some time. I’ve got a spread sheet that has case files for the past 7 years. Each case has a new row with open dates and close dates amoung other information.
I want to run a table that shows how many cases are open during any particular month. Jan 03 for example, or Jan 04, etc…
I’ve figured out how to log how many new cases have started within a particular month, but can not figure out how to measure how many cases are open within a particular month. Any help would be sincerely appreciated!
Kind regards,
James
James: What does ‘run a table’ mean? Do you want a count or a list or what? Send me some sample data by email and I’ll show you how to get what you want.
Dick: Sincere thanks for your offer to help! What I need is a creative count formula. I know the “countif” function that will allow me to see what cases opened on “1/9/07? (for example), however what I want to know is…
if column A is “Date Case Opened” and column B is “Date Case Closed” – what formula would be able to look at A2:A888 and B2:B888 to count all the cases that were open in Sept of 07, (for example).
Some of the cases are open for a month, some are open for a year…
Does this help explain the situation better?
Thanks again, in advance for your assistance!
James
Enter with Control+Shift+Enter because it’s an array formula. Here’s what it says in English:
Open date is before October AND (close date is after Sept OR no close date)
Dick!
Wow! This is the closet I’ve got yet to figuring this out!
“Open date is before October” is complete good, however,
“AND (Close date is after Sept OR no close date)” needs to have also the possibility of “OR Close date is within Sept”
This is where I come into problems because the only way I can figure this out is to put in an “OR Close date is (2007,9,1)OR Close date is (2007,9,2)…etc…etc…
Any further help would be sincerely appreciated!
James
Ok,
I see how this formula should work, even with dates that end within the month… however it doesn’t.
Cell A1: Start Date
Cel B1: End Date
Cell A2: 1-10-06
Cell b2:
Cell a3: 3-3-06
cell b3: 2-3-07
Cell A4: 1-12-06
cell b4: 2-1-07
cell a5:1-1-07
Cell b5: 15-1-07
This should give all cases that either start before or on, and end in or after
OR do not yet end.
Thanks again!
James
James:
This example uses January, not September. If I understand you correctly, you want the count of all projects that were open at any time in January. This formula tests:
Open date before February AND (Close date later than 31-Dec OR No close date)
With the data from your last two posts, it returns 4.
I have the same question as Jimbo and I noticed that no-one had answered it so I’m going to ask the question again. What happens if you need to have Week 1 as the start of the financial Year?
Steven (and Jimbo): I think this, but make sure you check it.
Dick:
Sincere thanks. I’ve finally realised the necessity for the “cntrl shift enter”… I know that you told me this at the start, but for some reason, i did not do this… Now that I have… it works great!
Kind regards!
James