BoostExcel main content

Week Numbers - Date Picker for Excel

Week numbers are commonly used in industries like logistics and manufacturing for the scheduling of work and deliveries. Although it sounds simple to show week numbers on the calendar, several complexities can arise depending on the desired outcome and context.

Types of Week Numbers

Week numbering systems vary globally, reflecting cultural, religious, and historical influences. According to Wikipedia, there are at least six numberings in use. The two most common types are ISO 8601 and Western traditional.

  • ISO 8601 (International Standard): In this widely used system, Monday is the first day of the week, followed by Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday as the seventh and final day. Week number 1 is the week that contains January 4.

    It may happen that the first week of the year contains a few last-day days from the previous year. For example, the first week of 2014 starts on December 30, 2013 (Monday) and ends on January 5, 2014 (Sunday).

  • Western tradition: About half the world’s population does not follow the ISO standard. Countries like the United States, Canada, Australia, and New Zealand start the week with Sunday on their calendars. Sunday is commonly viewed as the first day of the week, and Saturday is the last day of the week. Week number 1 is the week that contains January 1.
  • Most Middle Eastern countries use a calendar system (called Islamic in our calculator) in which the week with the number 1 in any given year is the one that contains January 1st. In this system, Saturday is the first day of the week, and Friday is the last.

The map of types of week numbers
wikipedia.org: World map showing the first day of the week used in different countries
Legend for the map

Each year contains at least 52 weeks. A quick calculation confirms that 52 (weeks) times 7 days (per week) equals 364 days, and there are 365 days in a year (366 in a leap year). The existence of the 53rd week depends on the year number and the week numbering system used, as shown in the example in the following section.

How to Get Week Numbers in Excel

In Microsoft Excel, you can determine the week number of a specific date using the WEEKNUM function. The function looks like this:

=WEEKNUM(serial_number, [return_type])
   
  • serial_number (required): A date within the week. You can enter dates using the DATE function or as results of other formulas.
  • return_type (optional): A number that determines which day the week begins. The default is 1 (Sunday).

For example, if cell A2 contains a date value of March 9, 2012:

  • =WEEKNUM(A2) (based on weeks beginning on Sunday) gives week 10.
  • =WEEKNUM(A2, 2) (based on weeks beginning on Monday) gives week 11.

If you specifically want the ISO week number (ranging from 1 to 53), you can use the ISOWEEKNUM function:

    =ISOWEEKNUM(A2)
   

For example, if the date in the A2 cell is December 31, 2023, "=WEEKNUM(A2,1)" will return 53, but "=ISOWEEKNUM(A2)" will return 52.

Remember that Excel stores dates as sequential serial numbers, with January 1, 1900, as serial number 1. For more details, you can refer to the official Microsoft Support article.

Date Picker for Excel (Pop-up Excel Calendar) is also a handy tool for you to find out the week number of a date. To show the week numbers, click Settings on the ribbon tab, and then choose Yes for the Show Week Numbers option on the Calendar page.

The Calendar page of the Settings window

Date Picker for Excel internally uses the MonthCalendar class. This class, by default, shows week numbers according to the settings in the Windows control panel. In addition to that, our app also offers the option "First day of week", which allows you to specify which weekday, from Monday to Sunday, is the starting day of a week.