Implementing Week-Numbering Systems and Date/Time Representations in Excel

Article
05/23/2014

**Summary:**Learn about the four different week-numbering systems available in Microsoft Office Excel and how to use them. Also learn why it is important to implement the date and time representation of the ISO8601:2000 standard in your Excel applications. (10 printed pages)

Ron de Bruin, Microsoft Excel MVP

Norman Harker, Senior Lecturer, University of Western Sydney

Frank Rice, Microsoft Corporation

February 2007

Applies to: Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, Excel 2007

Contents

About the Week-Numbering Systems in Excel
About ISO8601:2000 Standard Week Numbering
Implementing Week Numbering Using the WEEKNUM Function
About Simple Week Numbering
Download a Weekly Calendar
Conclusion

About the Week-Numbering Systems in Excel

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.

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.
Excel WEEKNUM function with an optional second argument of 1 (default). Week one begins on January 1st; week two begins on the following Sunday.
Excel WEEKNUM function with an optional second argument of 2. Week one begins on January 1st; week two begins on the following Monday.
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).

About ISO8601:2000 Standard Week Numbering

The ISO8601:2000 Standard for week numbering is becoming mandatory in the European Union, and it is commonly used throughout the world.

Note
There is no built-in worksheet function in Excel for ISO week numbering.

The ISO8601:2000 standard, first issued in 1986, states that dates must be represented as either:

yyyymmdd, such as 20050330 for March 30, 2005
yyyy-mm-dd, such as 2005-03-30 for March 30, 2005

The standard also states that week numbering should use the following algorithm, definable in one of two ways (both of which have the same result):

Week one begins on the Monday of the week in which January 4th occurs.
Week one begins on the Monday of the week that contains the first Thursday of the calendar year.

The ISO standard might pose difficulties for Excel 2007 users who are unfamiliar with the date representation methodology. Further, there is no built-in function or Analysis ToolPak function that supports the ISO week-numbering algorithm.

Advantages of the ISO8601:2000 Standard

There are four main advantages of the date representation defined in the ISO8601:2000 standard.

A sorted date representation in the yyyymmdd or yyyy-mm-dd format produces a correctly ordered series of dates.
A numeric date representation is clearer for users who may be unfamiliar with the English month names and their abbreviations (Jan., Feb., Mar).
A numeric date representation eliminates ambiguous date representations such as 12-11-2005, which can mean December 11th, 2005 (predominantly in the United States) or 12th November 2005 (predominantly in Europe).
Four-digit years clearly designate the century.

The ISO week-numbering algorithm might produce results that seem unusual to you. For example, week one will not necessarily begin on January 1st. Week one might begin as early as December 28th in the preceding calendar year. In addition, week 52 or week 53 might extend into the next calendar year as far as January 3rd.

The advantages of the ISO system are the following:

Every week is of equal length. Other systems produce weeks that have fewer than seven days at the beginning or the end of the year.
Every week begins on a Monday; day three of week seven is always a Wednesday.
There are always 52 or 53 weeks in a year. Other systems can create peculiar years that have 54 weeks.

Among the characteristics of the ISO system of particular importance to Excel users include the following:

All versions of Excel in all language editions (since Excel 1997) have yyyy-mm-dd as one of the standard date formats.
Dates are provided as strings in the various date functions. Use of the format yyyy-mm-dd ensures correct interpretation, regardless of the computer's regional settings and regardless of the double-digit year interpretation settings. Dates in formats other than yyyy-mm-dd might be interpreted differently, depending on the computer's regional settings and double-digit year interpretation settings. In many cases, the entry will be converted to a text entry, and as a result:
- It might not be obvious that the entry is a date.
- If used in a subsequent date calculation, the date serial number will be regarded as "0", and will be interpreted as "31-December-1899." For example, consider the entry of 03-07-05. This has one of six possible interpretations, depending upon your Regional Settings and double-digit year interpretation settings.
  
  03 July 2005
  
  03 July 1905
  
  07 March 2005
  
  07 March 1905
  
  05 July 2003
  
  05 July 1903
Other string inputs to date functions can result in varying formats or incorrect interpretations.

Since Excel 2000, users are advised against using string arguments as inputs into date functions. This is because of the different interpretations, depending on the computer's regional settings and double-digit year interpretation settings. By using the date format separation specified in the ISO standard, you can avoid these issues. For example, use the following.
```
=WEEKDAY("2005-02-23",1)
```
Not:
```
=WEEKDAY(DATE(2005,02,23),1)
```
To return the date "23-Feb-2005," use the following.
```
="2005-02-23"
```
You can also construct strings using the INDIRECT function.

If you use any other string format, you might have problems if the regional settings or the double-digit date interpretation settings on the computer using the workbook are different from the settings on the computer that created the workbook. The ISO standard string format represents a useful and easy way to hard code dates into formulas.
There is a problem with the TEXT function. The format string might not translate correctly if the workbook is opened in a different language version of Excel. For example, consider the following.
```
="Today is " & TEXT(TODAY(),"yyyy-mm-dd")
```
In the English language version of Excel, the formula returns the following:

Today is 2005-02-23

However, the Dutch-language version year format uses jaar "jjjj-mm-dd" so the formula returns the following:

Today is yyyy-02-23

A similar issue arises when you use the TEXT function in the Dutch-language version of Excel, and then open the workbook with an English-language version. These types of errors can exist wherever a date string is used that is formatted differently from ISO-standard date strings.
There are only two date entry forms that are unequivocally interpreted as the intended date by all languages and all versions of Excel. These are yyyy-mm-dd and yyyy/mm/dd.

There is no advantage to using the slash (/) separator as opposed to using the ISO-standard hyphen (-) separator, so it is best to use the ISO standard.
If you pre-format a range of cells as text, you can enter all dates as yyyy-mm-dd, including dates before 1900-01-01 (Or 1904-01-02 if you use the 1904 date system).

This facilitates the subsequent sorting of the dates into date order. If you do not pre-format the dates as text, sorting puts date serial numbers in date order and pre-1900 text dates in alphanumeric order.
By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system. Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system.

To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days) between the dates.

The following steps illustrate one way to compensate for this difference:
1. Type 1462 into any empty cell in the workbook.
2. Copy the contents by selecting the cell and pressing CTRL+C.
3. Select the cell that contains the date you want to update.
  
  Excel 2007 only:
  
  On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you are pasting to), and then click OK.
  
  -or-
  
  Excel 97, Excel 2000, Excel 2002, and Excel 2003 only:
  
  On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK.

For more information about working with dates before 1900-01-01 in Excel, see MVP John Walkenbach's XDate: Extended Date Functions Add-In.

Computing the Week Number of ISO-Standard Date Representations

In the ISO standard, week one can begin as early as December 29th of the preceding calendar year. All weeks have seven days and begin on a Monday. Some calendar years will have 52 weeks, and some will have 53 weeks.

The following formula (developed by the late Frank Kabel) returns the week number for the date in cell B4 based on the ISO standard.

=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)

All weeks begin on a Monday, with week one starting on Monday of the week that contains the first Thursday of the calendar year.

Another way to compute the week number is to use a user-defined function (UDF) in a standard module of a workbook. The following procedure (provided by Daniel Maher) demonstrates how to do this.

Note
The user's security settings must allow macros for this procedure to work.

In Excel 2007, on the Developer tab, click Visual Basic to open the Visual Basic Editor.

In Excel 97 through Excel 2003, click Tools, click Macro, and then click Visual Basic Editor.

You can press ALT+F11 in all versions.
On the Insert menu, click Module.

Type or paste the following function into the code window:

Public Function IsoWeekNum(d1 As Date) As Integer
' Provided by Daniel Maher.
   Dim d2 As Long
   d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
   IsoWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

On the File menu, click Close and return to Excel, or press ALT+Q to close the Visual Basic Editor.

For example, the following code calls the function in cell B4 in the worksheet.
```
=IsoWeekNum(B4)
```

For more information about the ISO standard and a sample workbook with examples showing how to use ISO dates or week numbers, see Ron de Bruin's ISO Date Representation and Week Numbering.

Implementing Week Numbering Using the WEEKNUM Function

You can also implement week numbering by using the WEEKNUM function. In Excel 2007, WEEKNUM is a standard built-in function. In Excel 97 through 2003, you must use the Analysis ToolPak add-in. After the add-in is installed, select the Analysis ToolPak option that is available when you click the Tools menu, and then click Add-Ins; otherwise, using the function returns the NAME? error.

Note
If you do not see the Analysis ToolPak option in the Add-Ins dialog box, you can install it from the Control Panel by clicking Add or Remove Programs (Programs and Features in Windows Vista), and then clicking Change on your Microsoft Office Professional installation. The option is not selected by default.

Caution
When you use the WEEKNUM function from the Analysis ToolPak add-in and open the workbook in a different language version of Excel, Excel will not translate the formula and display your formula as text. For example, assume that you open a workbook created in a Dutch language version of Excel in a computer with English as the Regional Settings language. If the workbook contains the Dutch version of the WEEKNUM function (WEEKNUMMER), when the formula is evaluated, Excel will display an #NAME? error.

When you use the WEEKNUM function from the Analysis ToolPak add-in and open the workbook in a different language version of Excel, Excel will not translate the formula and display your formula as text. For example, assume that you open a workbook created in a Dutch language version of Excel in a computer with English as the Regional Settings language. If the workbook contains the Dutch version of the WEEKNUM function (WEEKNUMMER), when the formula is evaluated, Excel will display an #NAME? error.

Potential Issues with the WEEKNUM Function

Algorithms using the Analysis ToolPakWEEKNUM function might introduce inaccuracies in your formulas because the first or last weeks may not include seven days. This could result in some years with 54 weeks.

The following examples illustrate potential problems that you might encounter by using the WEEKNUM function. These statements assume a date in worksheet cell B4:

=WEEKNUM(B4,1)

=WEEKNUM(B4)

Likewise, in these statements, week one begins on January 1st (regardless of which day that falls on), and week two begins on the following Sunday. This adds an additional week to the year.

=WEEKNUM(B4,2)

In this statement, week one starts on January 1st, and week two starts on the following Monday. This adds an additional week to the year.

Before using the function in your own calculations, you should test the results for inaccuracies.

Using a Replacement Formula for the Built-in WEEKNUM Function

Using a replacement formula or a user-defined formula (UDF) has the following advantages.

No problems if users of your workbook do not have the Analysis ToolPak add-in installed or selected in the list of available Excel add-ins.
The replacement formula or UDF works in every language version of Excel, whereas formulas used with the Analysis ToolPak will not translate if you open your workbook in a different language version of Excel.

To avoid these problems with the Analysis ToolPakWEEKNUM function, you can use the following replacement formulas:

(These samples were provided by Daniel Maher.)

Replace:

=WEEKNUM(B4,1)

With:

=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)

Or replace:

=WEEKNUM(B4,2)

With:

=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,0))))/7)

Or you can use the following UDF in a standard module of your workbook to calculate the week number.

Function VBAWeekNum(D As Date, FWDayArg As Integer) As Integer
' You can see examples of this at 
' http://www.cpearson.com/excel/weeknum.htm
   VBAWeekNum = CInt(Format(D, "ww", FWDayArg))
End Function

To use this UDF, add the following to a worksheet cell:

=VBAWeekNum(B4,1)

Week 1 starts on January 1; week 2 starts on the following Sunday.

=VBAWeekNum(B4,2)

Week 1 starts on January 1; week 2 starts on the following Monday.

About Simple Week Numbering

You can also use simple week numbering. If you use simple week numbering, remember that weeks will start on different days, and the year will always have a fifty-third week that has one or two days.

For example, the following formula assumes that a date in cell B4 results in week one beginning on January 1st, week two beginning on January 8th, and week 53 having only one or two days (for leap years).

=INT((B4-DATE(YEAR(B4),1,1))/7)+1

Download a Weekly Calendar

You can download a weekly calendar file from Ron de Bruin's Week Numbers in Excel. The weekly calendar file displays all of the dates and week numbers for a year on one printable page. There is also a separate sheet for all four-week numbering systems.

Conclusion

There are a number of ways to represent week numbers and dates in Excel. The method you choose should be based on a thorough examination of the requirements for your application and on the regional and date-specific settings on the computers where the workbook will be used.

Acknowledgements

John Green, Excel MVP, Sydney, Australia

Daniel Maher (Who posts to the Excel newsgroups as "Daniel M")

The late Excel MVP Frank Kabel

Additional References

You can find more information about week numbering and date representation in Excel from the following resources.

For a wealth of information about week-numbering implementations, see Chip Pearson's Week Numbers in Excel.
For comprehensive coverage of date-related Excel issues, see the index at Chip Pearson's Topic Index.
For a freely downloadable application that provides separate calendars for ISO-standard week numbers as well as three other common week-numbering systems and other information about week numbers, see Ron de Bruin's Week Numbers in Excel.
For more information and an example workbook that illustrates ISO dates and week numbers, see Ron de Bruin's ISO Date Representation and Week Numbering.

About the Authors

Ron de Bruin is an Excel Most Valuable Professional (MVP) and a frequent contributor to the newsgroups.

Norman Harker is a Senior Lecturer at the University of Western Sydney.

Frank Rice is a Microsoft employee and frequent contributor to the Office Developer Center.