Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

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.
    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 ATP function before Excel 2010 that supports the ISO week number

 

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

And the most efficient UDF for determining the ISO Week number from a date appears to be:

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

 

Download example workbook

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

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.

 

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.