The
WEEKNUM function in Excel calculates the week number of a year for a given date, returning an integer from 1 to 54. This calculation depends on two arguments: the serial number representing the date and a return type that defines the start day of the week and how the first week is counted. Understanding these parameters is crucial for accurate time-based reporting.
Syntax and Arguments
The basic syntax for the function is straightforward but requires precise argument handling to avoid off-by-one errors in your data analysis. The structure is as follows:
=WEEKNUM(serial_number, [return_type])
The
serial_number argument is required and represents the date you want to evaluate. In Excel, dates are stored as sequential serial numbers where January 1, 1900, is serial number 1. You can enter the date directly as text enclosed in quotation marks (e.g., "1/1/2023"), use a cell reference (e.g., A1), or generate it using other date functions like
TODAY() or
DATE(). If the serial number refers to a date before January 1, 1900, Excel will return a
#NUM! error.
The
return_type argument is optional but highly recommended for clarity. It determines which day the week starts on and which week is considered the first week of the year. There are two main systems used globally: the US system (Sunday start) and the ISO system (Monday start). Without this argument, Excel defaults to Type 1, which assumes Sunday is the first day of the week.
Understanding Return Types
Choosing the correct return type ensures your week numbers align with local business practices or international standards. The most common types are 1, 2, and 21.
Type 1 is the default setting. It considers Sunday as the first day of the week. The first week of the year is the one that contains January 1st. Consequently, if January 1st falls on a Monday through Saturday, that entire week is Week 1. However, if January 1st is a Sunday, it belongs to Week 52 or 53 of the previous year, making the current year start with Week 2.
Type 2 is critical for users in regions where the work week begins on Monday. It sets Monday as the first day of the week. Like Type 1, it assigns Week 1 to the week containing January 1st. This is often preferred in European business contexts because it aligns better with standard Monday-to-Friday work schedules.
Type 21 is the ISO 8601 standard code. This is arguably the most important type for financial and academic reporting. It sets Monday as the first day of the week, but it changes the rule for the first week. Under ISO rules, the first week of the year is the one that contains the first Thursday of January. Alternatively stated, it is the week with four days in the new year. This means that January 1st might fall in Week 52 or 53 of the previous year if it is a Friday, Saturday, or Sunday. This prevents fragmentation of weeks across calendar years in official records.
Other less common types include Type 11 (Monday start, same logic as Type 1 for year boundary) and Type 17 (Tuesday start). While Type 11 exists, it is rarely used compared to Type 2 or 21. Confusion often arises regarding Type 11; it behaves identically to Type 2 regarding the start day (Monday) but uses the "contains Jan 1" rule for week assignment rather than the ISO Thursday rule. Always verify your specific regional requirement before selecting a type.
Practical Examples
To illustrate the differences, consider the date January 1, 2024, which was a Monday.
Using
=WEEKNUM("1/1/2024", 1) returns 1. Since Sunday is the start day, and Jan 1 is in the week starting Dec 31 (Sunday), that week is Week 1.
Using
=WEEKNUM("1/1/2024", 2) returns 1. Monday is the start day, so the week starting Jan 1 is Week 1.
Using
=WEEKNUM("1/1/2024", 21) returns 1. The week containing Jan 1 has four days in 2024 (Mon-Thu), so it is the first ISO week.
Now consider January 1, 2025, which is a Wednesday.
=WEEKNUM("1/1/2025", 21) still returns 1 because the week containing Jan 1 includes the first Thursday (Jan 2).
However, look at December 31, 2024, a Tuesday.
=WEEKNUM("12/31/2024", 21) returns 1. Even though it is the last day of the calendar year, it falls in the week that contains the first Thursday of 2025 (Jan 2). Therefore, under ISO standards, this date belongs to Week 1 of 2025, not Week 53 of 2024. This distinction is vital when aggregating data by fiscal quarters or annual totals.
For more complex data manipulation involving week numbers, you may need to combine this with other formulas. For instance, if you are building a dashboard that pulls data from multiple sources, understanding how to handle these week indices correctly prevents misalignment. You might also find related techniques useful when managing column sizes or validating inputs based on these calculated values. See our guide on
Getting Started: Match Size of One Column to Another in Excel-Like Applications for layout tips, or check
Formula-Based Data Validation in Excel, Web Sheets, and More to ensure your date inputs are robust.
Common Pitfalls
One frequent error occurs when users assume all weeks are exactly seven days long in terms of numbering. Because the year start rule varies, some years have 52 weeks while others have 53. Using the wrong return type can shift dates into incorrect weeks, leading to inaccurate summaries in pivot tables or charts.
Another issue is formatting. The result of
WEEKNUM is a plain number. If you display this in a chart, ensure the axis treats it as a continuous scale rather than a category list if you are tracking trends over many years. Additionally, always use valid Excel date serials. Entering text that looks like a date but isn't recognized by Excel's date parser will cause errors. Use the
DATEVALUE function if you are importing raw text data to convert it properly before applying
WEEKNUM.
Frequently Asked Questions
What happens if I omit the return_type argument?
Excel defaults to Type 1, meaning Sunday is the first day of the week and the week containing January 1st is Week 1. This is the US standard but may not match your local business conventions.
Why does my week number jump from 52 to 1 in December?
This usually happens when using Type 21 (ISO) or Type 1/2 depending on the year. If January 1st falls late in the week, the preceding days belong to the previous year's final week. Conversely, the end of December might belong to Week 1 of the next year under ISO rules.
Can WEEKNUM handle dates outside 1900-2078?
No. Excel’s date system starts at January 1, 1900. Any date prior to this will result in a #NUM! error. For historical data before 1900, you must calculate week numbers manually or use alternative programming methods.
How do I get the Monday-starting week for the US market?
Use Type 2. This sets Monday as the start day but keeps the "week containing Jan 1" rule for Week 1 designation, which is often preferred for internal reporting even in the US.
Is there a difference between WEEKNUM and ISOWEEKNUM?
Yes.
ISOWEEKNUM is essentially equivalent to
WEEKNUM(date, 21). It strictly follows ISO 8601 standards. If you need standardized international week numbers,
ISOWEEKNUM is safer and more explicit than relying on remembering Type 21.