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,

Dates must be represented in either:

1. separated form of yyyy-mm-dd (eg 2005-03-30 for 30-03-2005) The ISO Standard agreed separator = "-"
2. separated form of yyyy/mm/dd (eg 2005/03/30 for 30-03-2005)

Week numbering should use the following algorithm that can be defined in one of two ways:

1. Week 1 starts on the Monday of the week in which 4th January falls.
2. 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:

1. non-familiarity with the system of date representation.
2. the fact that there is no built in or ATP function before Excel 2010 that supports the ISO week number

There are four main advantages of the ISO date representation:

1. Old hands at computing will readily appreciate that a sorted date representation yyyy/mm/dd 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:

• Every week will be of equal length. Other systems produce weeks with less than 7 days at the beginning and / or the end.
• Every week will start on a Monday. Day 3 of Week 7, will always be a Wednesday.
• 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)

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:

1. It will not often be immediately obvious that the entry is not regarded by Excel as a date.
2. 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: Note: 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: So there are 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.

Here are the UDFs for the two most useful functions that facilitate the implementation of the Standard, ISOYEARSTART and IsoWeekNumber. For a example how to use the UDFs download the 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

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.