{"id":1224,"date":"2009-06-30T03:00:00","date_gmt":"2009-06-29T19:00:00","guid":{"rendered":"http:\/\/vm-officeblogs.cloudapp.net\/2009\/06\/30\/week-numbers-in-excel\/"},"modified":"2022-08-05T07:57:19","modified_gmt":"2022-08-05T14:57:19","slug":"week-numbers-in-excel","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2009\/06\/30\/week-numbers-in-excel\/","title":{"rendered":"Week Numbers in Excel"},"content":{"rendered":"

Today’s author is, Ron de Bruin, an Excel MVP<\/a>. You can find more useful tips from Ron and links to Excel add-ins at his website: http:\/\/www.rondebruin.nl\/<\/a><\/em><\/p>\n

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:<\/p>\n

1)\u00a0 ISO Week number: The International Organization for Standardization (ISO) ISO8601:2000 Standard.<\/b>
\nAll weeks begin on a Monday. Week one starts on Monday of the first week of the calendar year with a Thursday.
\n2)\u00a0 Excel WEEKNUM function with an optional second argument of 1 (default).<\/b>
\nWeek one begins on January 1st; week two begins on the following Sunday.
\n3)\u00a0 Excel WEEKNUM function with an optional second argument of 2.<\/b>
\nWeek one begins on January 1st; week two begins on the following Monday.
\n4)\u00a0 Simple week numbering.<\/b>
\nWeek one begins on January 1st, week two begins on January 8th, and week 53 has only one or two days (for leap years).<\/p>\n

Note:<\/b> Excel does not have a standard worksheet function for the ISO week number and simple week numbering system.<\/p>\n

Worksheet Formulas for Week Numbers<\/h3>\n

The following sections assume that you have a date in cell B4<\/b> for testing the week number formulas.<\/p>\n

<\/h3>\n
ISO Week Numbers<\/h5>\n

There is no built-in worksheet function for ISO week numbers in Excel. Copy the following formula and paste it in a worksheet cell to return an ISO week number:<\/p>\n

=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)\/7)<\/strong><\/p>\n

Alternatively, you can open the Visual Basic editor, click Module<\/strong> on the Insert<\/strong> menu, and then copy this user-defined function (UDF) into the module. After adding this UDF to your workbook, you can use it like a built-in function =IsoWeekNumber(B4)<\/strong><\/p>\n

Public<\/span> Function<\/span> IsoWeekNumber(d1 As<\/span> Date<\/span>) As<\/span> Integer<\/span>\n' Attributed to Daniel Maher<\/span>\n    Dim<\/span> d2 As<\/span> Long<\/span>\n    d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)\n    IsoWeekNumber = Int((d1 - d2 + WeekDay(d2) + 5) \/ 7)\nEnd<\/span> Function<\/span>