ISO Date Representatation and Week Numbering
ISO8601: 2000 is becoming mandatory in the European Union and will become more commonly
used throughout the World.
The Standard, first issued as long ago as 1986, prescribes amongst other things,
1. Dates must be represented in either:
a. non-separated form of yyyymmdd (eg 20050330 for 30-Mar-2005)
b. separated form of yyyy-mm-dd (eg 2005-03-30 for 30-Mar-2005)
2. Week numbering should use the following algorithm that can be defined in one of two ways:
a. Week 1 starts on the Monday of the week in which 4th January falls.
OR (alternative definition that means the same)
b. Week 1 starts on the Monday of the week that contains the first Thursday of the calendar year.
For Excel users, the main difficulties of the Standard are:
a. non-familiarity with the system of date representation.
b. the fact that there is no built in or Analysis ToolPak function that supports the ISO week number algorithm.
The following is an illustration from the free workbook that covers Excel implementation of the
Standard can be downloaded from the link below:
The workbook contains comprehensive formulas that assist in ISO implementation,
an ISO weeknumber calendar creator, and examples of use of two VBA User Defined Functions.
Download workbook (File date 2005-03-30)
Note however, that the operation of the workbook's User Defined Functions requires that the Security
settings should be set at Medium or Low. This is not usually recommended except for opening workbooks
from known and trusted sources
Comment on the ISO Standard.
There are four main advantages of the ISO date representation:
1. Old hands at computing will readily appreciate that a sorted date representation yyyymmdd
or yyyy-mm-dd will produces a correctly ordered series of dates. The fact that the numbers
are not a complete numerical sequence does not adversely impact upon the sequence.
2. By adopting a numeric representation, there can be no language based confusion that results from
use of long or short alphabetical names of the Months.
3. There will be an end to the confusion that results from (predominantly US) dates of 12-11-2005
for 11th December 2005 compared to the (predominantly European) form that interprets
12-11-2005 as 12th November 2005.
4. By using four digits for the year number, there will be no confusion as which Century the date falls.
The week numbering algorithm looks strange. It means that Week 1 will not start on 1st January.
Week 1 may start as early as 28th December in the preceding Calendar Year and that Weeks 52
or 53 may extend into the next Calendar Year as far as 3rd January.
The advantage of the ISO week number system are:
1. Every week will be of equal length. Other systems produce weeks with less than 7 days at the beginning
and / or the end.
2. Every week will start on a Monday. Day 3 of Week 7, will always be a Wednesday.
3. There will always be 52 or 53 ISO weeks in a year. Other systems can create peculiar years where there
can be 54 weeks.
Excel related aspects of date representation:
1. All versions of Excel in all language editions from at least 1997 have yyyy-mm-dd as one
of the standard date formats.
2. Where dates are provided as strings in the various date functions, use of the string format
yyyy-mm-dd will always be interpreted correctly irrespective of the Regional Settings and the
double digit year interpretation setting.
With all other string inputs to date functions, the result will vary or will fail to be interpreted as the date intended. Since Excel 2000, Help has advised against using string arguments for dates in Date functions.
The reason for that advice is different interpretations depending upon Regional Settings and double digit
year interpretation setting.
If you use the ISO separated date format, you can reject that advice and use (eg)
=WEEKDAY("2005-02-23",1)
Rather than:
=WEEKDAY(DATE(2005,02,23),1)
Similarly, you can use:
=”2005-02-23” to return the date 23-Feb-2005
Strings may also be constructed using the INDIRECT function.
If you use any other string form in this way, it will all end in tears if Regional settings or Double Date interpretation settings on the computer that the workbook is opened are different from the ones that
existed on the original computer the workbook was built on.
Not much use on its own! But the form represents a useful and easy way to “hard code” dates into formulas.
3. Entry of a date using yyyy-mm-dd will always be correctly interpreted as the date intended by the user.
Other date entry forms may be interpreted differently depending upon Regional Settings and double
digit year interpretation settings.
In many cases the entry will be converted to a text entry and as a result:
a. It will not often be immediately obvious that the entry is not regarded by Excel as a date.
b. If used in a subsequent date calculation, the date serial number will be regarded as 0, and will be
interpreted as 31-December-1899.
Consider the entry of 03-07-05.
This has one of one of 6 possible interpretations depending upon Regional Settings and
double digit year interpretation setting:
03 July 2005
03 July 1905
07 March 2005
07 March 1905
05 July 2003
05 July 1903
4. One problem that exists with the TEXT function.
The format string will not translate if the workbook is opened on a different language version of Excel.
For example:
English language Version entry:
="Today is "&TEXT(TODAY(),"yyyy-mm-dd")
In English language version the formula returns:
Today is 2005-02-23
Fails when the when workbook is open in Dutch language version (year = jaar "jjjj-mm-dd").
With a Dutch language version the formula will return:
Today is yyyy-02-23
The same effect arises when you use TEXT in the Dutch language version of Excel and then try and
open the workbook with an English language version. These errors exist whatever date string is used
and is not peculiar to ISO date strings.
5. There are only two date entry forms that will be unequivocally interpreted as the intended date
by all language and all versions of Excel:
yyyy-mm-dd
and
yyyy/mm/dd
(But why use the “/” form when the ISO Standard agreed separator is “-“?)
6. If you pre-format a range of cells as text, you can enter all dates as yyyy-mm-dd including dates that
are before 1900-01-01 (Or 1904-01-02 if you use the 1904 Date System).
This allows subsequent sorting of the dates into date order. Without pre-formatting as text, sorting
will sort date serial numbers in date order and (pre-1900) text dates in alphanumeric order.
The two most useful UDFs
Here are the UDFs for the two most useful functions that facilitate the implementation of the Standard:
For a example how to use the UDFs download the example workbook.
Here’s the UDF for finding ISOYEARSTART :
Public Function ISOYEARSTART(WhichYear As Integer) As Date
' First published by John Green, Excel MVP, Sydney, Australia
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARSTART = NewYear - WeekDay
Else
ISOYEARSTART = NewYear - WeekDay + 7
End If
End Function
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
Acknowledgements:
John Green, Excel MVP, Sydney, Australia. John was the originator the ISOYEARSTART function above.
Our formulas for determining ISO Year Start are derived from that function.
Daniel Maher (Who tends to post under the title "Daniel M"). Similarly, our formulas for week numbering
are derived from that function.
More Information
Implementing Week-Numbering Systems and Date/Time Representations
http://msdn.microsoft.com/en-us/library/bb277364.aspx
Week numbers in Excel
http://www.rondebruin.nl/weeknumber.htm
You should refer to Chip Pearson's web site for an exposition on Week Number implementation
http://www.cpearson.com/excel/weeknum.htm
And generally see Chip's Index for a comprehensive coverage of date related Excel issues:
http://www.cpearson.com/excel/topic.htm