Master the WEEKNUM Function: Complete Guide to Week Number Calculations in Excel
=WEEKNUM(serial_number, [return_type])The WEEKNUM function is a powerful date and time formula in Excel that allows you to determine which week of the year a specific date falls into. This intermediate-level function is essential for professionals working with project management, financial reporting, and scheduling applications where week-based analysis is critical. Whether you're tracking project timelines, analyzing sales data by week, or managing inventory across fiscal periods, WEEKNUM provides a straightforward way to extract week numbers from any date. Understanding WEEKNUM becomes increasingly valuable when you need to create week-based reports, compare data across different years, or automate scheduling tasks. The formula supports two different numbering systems through its optional return_type parameter, allowing you to align with either the ISO 8601 standard or traditional American week numbering. By mastering this formula, you'll enhance your data analysis capabilities and create more sophisticated date-based calculations that can significantly improve your Excel productivity and reporting accuracy.
Syntax & Parameters
The WEEKNUM formula follows the syntax =WEEKNUM(serial_number, [return_type]), where both parameters work together to deliver precise week number calculations. The serial_number parameter is required and represents the date you want to analyze. This can be entered as a date value, a cell reference containing a date, or a formula that returns a date. Excel stores dates as serial numbers internally, so WEEKNUM automatically interprets date values correctly. The return_type parameter is optional and determines which numbering system Excel uses to calculate the week number. When return_type is 1 or omitted (default), the week containing January 1st is week 1, and weeks run from Sunday to Saturday. When return_type is 2, weeks run from Monday to Sunday, also starting with the week containing January 1st. Return_type 21 applies the ISO 8601 standard, where week 1 is the first week with four or more days in the new year, and weeks always run Monday to Sunday. Understanding these return_type options is crucial because they significantly affect your results, especially for dates near year boundaries. For example, December 31st might be in week 52 or week 1 of the next year depending on your return_type selection.
serial_numberreturn_typePractical Examples
Project Management: Tracking Weekly Milestones
=WEEKNUM(DATE(2024,3,15),1)This formula calculates the week number for March 15, 2024, using the American standard (return_type=1, Sunday-Saturday). The DATE function creates the date value, ensuring proper date recognition. This returns 11, meaning the project starts in the 11th week of 2024.
Sales Analysis: Weekly Revenue Comparison
=WEEKNUM(A2,21)This formula extracts the ISO week number from the date in cell A2. Using return_type 21 ensures compliance with international standards where week 1 always contains the first Thursday of the year. This is particularly useful for multi-national companies that need standardized week numbering across regions.
Inventory Management: Stock Rotation by Week
=WEEKNUM(B5,2)This formula uses return_type 2 (Monday-Sunday week structure) to identify the week number for the shipment date in cell B5. The Monday-Sunday structure aligns with many European business practices and helps coordinate with weekly inventory audits typically scheduled on Mondays.
Key Takeaways
- WEEKNUM calculates which week of the year a date falls into, with support for three different numbering systems through the return_type parameter
- Return_type 1 (default) uses Sunday-Saturday weeks; return_type 2 uses Monday-Sunday weeks; return_type 21 uses ISO 8601 international standard
- Always explicitly specify return_type to ensure consistent results across Excel versions and when sharing workbooks with other users
- WEEKNUM is most powerful when combined with other functions like YEAR, CONCATENATE, or SUMIF for creating week identifiers, week-based analysis, and dynamic reporting
Pro Tips
Always specify the return_type parameter explicitly, even if using the default. This makes your formulas self-documenting and prevents unexpected results when spreadsheets are shared across different Excel versions or regional settings.
Impact : Reduces formula maintenance issues and makes your spreadsheets more portable and professional.
Create a helper column with WEEKNUM formulas when working with large datasets, rather than embedding WEEKNUM in complex calculations. This improves performance and makes debugging easier.
Impact : Significantly improves spreadsheet calculation speed and makes formula troubleshooting more straightforward.
Combine WEEKNUM with conditional formatting to visually highlight current or target weeks. Use rules like =WEEKNUM($A1,1)=WEEKNUM(TODAY(),1) to automatically highlight the current week across your entire dataset.
Impact : Creates dynamic, visually intuitive dashboards that automatically update and help users quickly identify relevant data.
Use WEEKNUM with OFFSET or INDEX/MATCH to create dynamic weekly reports that automatically pull data for specific weeks. This creates flexible reporting templates that require minimal manual updates.
Impact : Dramatically reduces report generation time and minimizes manual data entry errors in recurring reports.
Useful Combinations
Create Unique Week Identifiers with YEAR and CONCATENATE
=CONCATENATE(YEAR(A1),"-W",TEXT(WEEKNUM(A1,21),"00"))This combination generates formatted week identifiers like '2024-W11' by combining the year, a 'W' prefix, and the zero-padded ISO week number. Extremely useful for creating unique keys in databases, pivot tables, or reports that need to distinguish weeks across multiple years.
Calculate Week-over-Week Analysis with SUMIF and WEEKNUM
=SUMIF($A$2:$A$100,WEEKNUM(A2,1),$B$2:$B$100)This formula sums all values from column B where the week number matches the week number of the date in A2. Perfect for analyzing weekly totals, comparing same weeks across different years, or aggregating data by week for trend analysis.
Identify Current Week Dates with WEEKNUM and IF
=IF(WEEKNUM(A1,1)=WEEKNUM(TODAY(),1),"This Week","Other Week")This conditional formula identifies whether a date falls in the current week by comparing its week number to today's week number. Useful for highlighting current activities in project management, prioritizing tasks, or filtering recent data in dashboards.
Common Errors
Cause: The serial_number parameter contains text that Excel cannot interpret as a date, such as 'March 15' without proper date formatting, or the return_type parameter is outside the valid range (1, 2, or 21).
Solution: Ensure the date is properly formatted using DATE(), DATEVALUE(), or a recognized date format. Verify return_type is either 1, 2, or 21. Use =WEEKNUM(DATEVALUE(A1),1) if A1 contains text dates.
Cause: The serial_number is a number outside Excel's valid date range (negative numbers or numbers greater than 2958465, representing dates beyond December 31, 9999).
Solution: Check that your date value falls within Excel's supported range (January 1, 1900 to December 31, 9999). Verify the calculation producing the serial_number doesn't result in an out-of-range value.
Cause: The formula references a cell that has been deleted or moved, or the cell reference is incorrect in a copied formula.
Solution: Verify all cell references exist and contain valid date values. Use absolute references ($A$1) for fixed date sources when copying formulas across multiple rows. Check the formula bar to confirm the reference is correct.
Troubleshooting Checklist
- 1.Verify the date value is recognized by Excel as a date (check cell format; dates should be right-aligned by default)
- 2.Confirm return_type is one of the valid options: 1, 2, or 21; any other value produces #NUM! error
- 3.Check for text-formatted dates by using ISNUMBER() to verify the serial_number is numeric; convert text dates using DATEVALUE()
- 4.Test formulas near year boundaries (late December/early January) separately, as week numbers change dramatically in these periods
- 5.Verify cell references haven't been accidentally deleted when copying formulas; use absolute references ($A$1) for fixed date sources
- 6.Confirm your return_type choice aligns with your reporting requirements; document which system you're using for consistency across the workbook
Edge Cases
December 31st in years where it falls on Monday-Thursday with ISO 8601 (return_type 21)
Behavior: December 31st belongs to week 1 of the next year because ISO 8601 week 1 contains the first Thursday of the year
Solution: Account for this in year-end reports by checking if WEEKNUM returns 1 for late December dates; combine with YEAR function to identify year transitions
This is correct behavior per ISO 8601 standard, not an error
January 1st in years where it falls on Friday-Sunday with return_type 1
Behavior: January 1st is assigned to week 1, but previous days in December are week 1 of the current year (not week 53 of prior year)
Solution: Verify expected behavior for your use case; if you need strict year-based week numbering, consider using ISOWEEKNUM or adjusting return_type
This reflects how American week numbering assigns the first partial week to the current year
Leap years and February 29th dates
Behavior: WEEKNUM handles leap years correctly; February 29th produces valid week numbers just like any other date
Solution: No special handling needed; Excel's date system automatically accounts for leap years
This is not an error condition; Excel manages leap year calculations transparently
Limitations
- •WEEKNUM cannot distinguish between multiple occurrences of the same week number across different years without combining it with YEAR function; you must create composite identifiers for multi-year analysis
- •The function only returns week numbers (1-53); it doesn't provide week start/end dates or other week-related information without additional formulas
- •Return_type 21 (ISO 8601) is only available in Excel 2010 and later; workbooks using ISO week numbering are incompatible with Excel 2007
- •WEEKNUM uses Excel's internal date system, which doesn't recognize dates before January 1, 1900; historical data predating this requires manual conversion or alternative approaches
Alternatives
Simpler syntax with automatic ISO 8601 compliance; no return_type parameter needed. Produces consistent results across all Excel versions supporting it.
When: Use ISOWEEKNUM when you exclusively need ISO standard week numbering for international reporting or when simplicity is prioritized over flexibility.
Compatibility
✓ Excel
Since 2007
=WEEKNUM(serial_number, [return_type]) - Full support in all versions from 2007 through 365; return_type 21 (ISO) supported from 2010 onward✓Google Sheets
=WEEKNUM(date, [mode]) - Syntax is identical; mode parameter works the same as return_typeGoogle Sheets provides equivalent functionality with identical results; formulas are directly portable between Excel and Google Sheets
✓LibreOffice
=WEEKNUM(date, [mode]) - Function available with same parameters and behavior as Excel