 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
There are four primary week numbering systems in use worldwide. Each
system has subtle differences that you should be aware of. Excel can work
with any of these systems.
1) ISO Week: The
International Organization for Standardization (ISO) ISO8601:2000 Standard.
All weeks begin on a Monday. Week one starts on Monday of the first week of
the calendar
year with a Thursday.
2)
Excel WEEKNUM function with an optional second argument of 1 (default).
Week one begins on January 1st; week two begins on the following Sunday
3) Excel WEEKNUM function with an optional second
argument of 2.
Week one begins on January 1st; week
two begins on the following Monday.
4) Simple week
numbering.
Week one begins on January 1st, week two
begins on January 8th, and week 53 has only
one or
two days(for leap years).
Note: Excel do not have a
standard worksheet function for the ISO week number before Excel 2010 and
also not for the simple week numbering system.
With the Date in cell B4 you can test the formulas below
ISO Week Numbers
Note: There is no built-in worksheet function for ISO weeks in Excel before Excel 2010, the formula and the function below will work in all Excel versions so it is a good option to use in your workbook if it will be used in different Excel versions. But FYI In Excel 2010(and also in Excel 2011 for the Mac) they add a argument of 21 to the Excel WeekNum function to get the ISO week number as result : =WEEKNUM(A1,21) and in Excel 2013 they finally add a new function named ISOWEEKNUM
Copy the worksheet function below and paste it in a worksheet cell.
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)
You can also copy this UDF in a standard module and use this function
=IsoWeekNumber(B4)
Public Function IsoWeekNumber(d1 As Date) As Integer
' Attributed to Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
IsoWeekNumber = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function
The Excel worksheet function WEEKNUM
Reliance on Analysis ToolPak is a major annoyance because the Addin may
not be installed or checked in Excel versions before Excel 2007. A default
Excel installation has it unchecked. Also, there are international
difficulties where you use ATP formulas because these formulas are not
translated by Excel if you open the workbook in a different Excel language
version.
Note: In Excel 2007 and up WEEKNUM is a
Standard worksheet function so you not have the problems above if you share
your workbook between different Excel 2007-2013 language versions and if the
add-in is unchecked.
You can
use this two replacement functions from Daniel M to avoid problems above so
they work in every Excel version.
Replacing =WEEKNUM(B4,1)
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)
Replacing =WEEKNUM(B4,2)
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,0))))/7)
Simple Week Numbering
Note: There is no built-in worksheet function for Simple
week numbering in Excel.
Copy the function below and paste it in a
worksheet cell.
=INT((B4-DATE(YEAR(B4),1,1))/7)+1
The week calendar file shows you all the dates and week numbers from a
certain year on one printable page. If you want to have a week calendar from
an other year you only have to change one cell (the year). There is a
separate sheet for the following week numbering systems :
1)
ISO Week numbering: Week 1 starts on Monday of the week with the first
Thursday of the Calendar Year.
2) Excel WEEKNUM
function (optional second argument of 1 (default)). Week 1
starts 1-Jan with subsequent weeks starting on a Sunday and final week
ending on 31-Dec.
3) Excel WEEKNUM function
(optional second argument of 2). Week 1 starts
1-Jan
with subsequent weeks starting on a Monday and final week ending on 31-Dec.
4) Simple week number. Week 1 starts on the first day
of the year.
Download the Calendar file developed by Ron
de Bruin and Norman Harker
Week Numbers Calendar
version 2
Information about Week Numbers Calendar Version 2.
The Calendar is working with the 1900 and 1904 date system and don't have
a problem with the Excel 1900 Leap Year error. Excel (for compatibility
reasons) followed the error made by earlier spreadsheet
packages which
was to show a 29-Feb in 1900. Apart from now skipping 29-Feb-1900, our
calendar also allows reading of the correct day names for dates before
1-Mar-1900. Although week numbers are unlikely to be wanted for 1900, it
does now mean that there is now a source for a correct 1900 calendar.
We would like to acknowledge general reference on all date issues to:
Chip Pearson:
http://www.cpearson.com/excel/topic.aspx
Dave McRitchie:
http://dmcritchie.mvps.org/excel/excel.htm
Daniel Maher
has also published numerous simplifications of date formulas some of which
were used or adapted in producing the calendar.
The late
Frank Kabel created the ISO week number worksheet function on this
page
The base formula used for ISO year start were derived from a UDF
written by
John Green, Sydney.