To get a week number from a date in Excel, use =WEEKNUM(A2,2) for weeks that start on Monday, or =ISOWEEKNUM(A2) for ISO 8601 week numbers. Replace A2 with the cell containing your date; the right formula depends on how your organization defines the first week and the first day of the week.
Choose the week-numbering rule first
There is no single universal definition of “week of the year.” In one system, weeks start on Sunday; in another, they start on Monday. Systems also differ in what counts as week 1. Excel offers both ordinary week numbering and ISO week numbering, so choose a rule before using week numbers for reports, schedules, or comparisons.
The key distinction is how the first week is determined. In the standard WEEKNUM system, week 1 is the week containing January 1, even if that week has only a few days in the new year. The chosen return type determines which day begins each week. Under the ISO 8601 convention, weeks begin on Monday and week 1 is the week containing the first Thursday of the year. Equivalently, it is the first week with at least four days in the new year.
These definitions can assign a date near New Year’s Day to different week numbers—and, under ISO rules, to a different week-year. A result of 1 does not necessarily mean that the date belongs to the first ISO week, so check the convention expected by the report or recipient.
Use WEEKNUM for regular week numbering
WEEKNUM returns a number from 1 onward for a date. Its second argument, called the return type, specifies the weekday that starts the week and, for some options, the numbering system. If you omit that argument, Excel uses type 1, which starts weeks on Sunday.
With the date in A2 and weeks intended to start on Monday, enter this formula in B2:
=WEEKNUM(A2,2)
For a date of June 12, 2024, the result is 24. In this example, January 1, 2024 is a Monday, and June 12 falls in the 24th Monday-starting week under the week-containing-January-1 rule. If your workplace uses Sunday-starting weeks instead, and the date is still in A2, use this formula in B2:
=WEEKNUM(A2,1)
For June 12, 2024, that also returns 24, though other dates—especially dates near the beginning or end of a year—can produce different results depending on the start day.
Common return types include:
- 1: Sunday is the first day of the week; week 1 contains January 1. This is the default.
- 2: Monday is the first day of the week; week 1 contains January 1.
- 11 through 17: Set Monday through Sunday, respectively, as the first day of the week, while retaining the week-containing-January-1 numbering rule.
- 21: Use the ISO 8601 week system, with Monday as the first day and the first-Thursday rule for week 1.
Types 2 and 11 both start a week on Monday, but type 21 is different because its week-1 rule is also different. Selecting Monday as the start day alone does not make a result ISO-compliant.
Use ISOWEEKNUM for ISO 8601 weeks
For a report that specifically requests ISO week numbers, ISOWEEKNUM is often the clearest choice. It returns the ISO week number for a date. The function is available in Microsoft 365 and current Excel versions; older Excel releases may not include it.
With a date in A2, enter this formula in B2:
=ISOWEEKNUM(A2)
For June 12, 2024, the result is 24. You can also request the ISO system through WEEKNUM. With the same date in A2, enter this alternative in B2:
=WEEKNUM(A2,21)
That also returns 24 for June 12, 2024. Using ISOWEEKNUM can make the intent easier for another person to recognize, while WEEKNUM with type 21 can be convenient when a workbook already uses WEEKNUM for other conventions.
Understand the week-year around New Year
An ISO week number and a calendar year do not always belong together. For example, January 1, 2021 was a Friday. Under ISO rules, it belongs to week 53 of the ISO week-year 2020. A formula that displays the calendar year 2021 beside the ISO week number 53 would therefore create a misleading label.
With January 1, 2021 in A2, this formula in B2 returns the ISO week number, 53:
=ISOWEEKNUM(A2)
With the same date in A2, this formula in C2 returns the ISO week-year, 2020:
=YEAR(A2-WEEKDAY(A2,2)+4)
The calculation moves from the date to the Thursday in its Monday-to-Sunday week; the year of that Thursday is the ISO week-year. For a report, keep the ISO week number and ISO week-year together rather than pairing an ISO result with the ordinary calendar year. This matters for sorting, grouping, and comparing weekly data that crosses December and January.
Enter the formula and fill it down
In a simple worksheet, put dates in column A, starting at A2, and put the week-number formula in B2. Then fill the formula down alongside the dates. In Microsoft Excel for Microsoft 365 on Windows, select B2 and drag the fill handle at the lower-right corner of the cell down the column, or copy B2, select the destination cells, and paste. Excel adjusts the relative reference from A2 to A3, A4, and so on.
- Check that the cells in column A contain actual Excel dates, not text that merely looks like a date.
- Choose Sunday-start, Monday-start, or ISO numbering based on the reporting requirement.
- Enter the corresponding formula in the first result row and confirm its result with a known date.
- Fill the formula down and use the week-year as well if you are grouping ISO weeks across years.
The formulas and ribbon workflow are essentially the same in Excel for Mac and Excel for the web. The fill handle is available in those versions too, although its appearance and the surrounding interface can vary. Formula separators are usually commas in the examples here; depending on regional settings, Excel may expect semicolons instead.
Check date values and result formatting
Excel calculates week numbers from dates stored as numeric date values. A cell can display a date while actually containing text—for example, after data has been imported or copied from another system. If the formula returns an error or an unexpected result, check whether Excel recognizes the entry as a date. Re-entering the value in a recognized date format or converting the source data may resolve the problem.
WEEKNUM and ISOWEEKNUM return numbers, not date labels. If the result appears as a date, change the result cell’s format to General or Number. In Excel for Microsoft 365 on Windows, select the result cells and choose Home > Number Format, then select General or Number. The equivalent number-format controls are also available in Excel for Mac and Excel for the web, though their placement may differ.
Also check the date itself. A value interpreted using a different regional order can be a valid date but not the date you meant—for example, a day and month may be swapped. Confirm the displayed date and, when necessary, inspect the source format before trusting the week number.
Common mistakes and how to avoid them
- Leaving out the return type: WEEKNUM then uses Sunday as the first day of the week. State the return type explicitly when the reporting rule matters.
- Assuming Monday means ISO: WEEKNUM with type 2 starts weeks on Monday but still assigns week 1 to the week containing January 1. Use ISOWEEKNUM or type 21 for ISO rules.
- Combining week numbers from different years: Week 12 occurs every year. Group or label weekly data with a year as well as a week number.
- Using the calendar year with an ISO week: Some late-December dates belong to ISO week 1 of the next year, and some early-January dates belong to the last ISO week of the prior year.
- Assuming every business follows the same calendar: A company may define weeks to start on a different day or use a fiscal calendar. Excel’s standard options do not automatically know an organization’s custom rule.
For a custom fiscal calendar, first obtain the organization’s exact definition of week 1, the week-start day, and any 4-4-5 or other period rules. Do not substitute an ISO week number unless the business rule actually calls for ISO weeks.
Which formula should you use?
Use the rule that matches the data’s intended meaning, not simply the formula that produces a familiar number. WEEKNUM with type 1 is useful when the week starts on Sunday and the week containing January 1 is week 1. WEEKNUM with type 2 is useful for Monday-starting weeks that use that same January 1 rule. ISOWEEKNUM is the direct choice when a system or report specifies ISO 8601.
If you receive weekly figures from another source, ask which convention it uses before comparing results. A value labeled week 1 may refer to different dates under different systems. Consistent definitions are especially important when combining exports from multiple departments, databases, or countries.
Frequently Asked Questions
What is the simplest Excel formula for week number?
With a date in A2, use =WEEKNUM(A2) for Excel’s default Sunday-start system. Add a return type when you need a different start day or ISO numbering.
How do I get the week number starting Monday?
With the date in A2, use =WEEKNUM(A2,2). This starts weeks on Monday, but week 1 is still the week containing January 1; use ISOWEEKNUM for the ISO definition.
How do I calculate an ISO week number in Excel?
With the date in A2, use =ISOWEEKNUM(A2). Alternatively, =WEEKNUM(A2,21) uses the ISO week system.
Why does Excel show a different week number than another calendar?
The two tools may use different first days of the week or different definitions of week 1. Compare their settings and check dates near New Year, where the systems commonly diverge.
Can a week number identify a date’s year?
No. Week numbers repeat every year, and an ISO week-year can differ from the calendar year. Keep the appropriate year alongside the week number when labeling or grouping records.
Related
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
- Cash Flow Format in Excel: Build a Simple Statement
- CAGR Return in Excel: Calculate Investment Growth
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average If Excel Date Range? Formulas That WorkTo average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a…
- AVERAGEIF Across Sheets in Excel: Formulas & ExamplesExcel’s AVERAGEIF function cannot directly average matching values across multiple worksheets in one formula. Assuming Jan and Feb…
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- Excel MID String: How to Extract Text by Position in CellsIn Excel, use the MID function to extract a specified number of characters from the middle of a…
- Excel IFS Greater Than and Less Than: ExamplesYes. In Excel, use greater-than and less-than operators directly inside an IFS logical test, such as A2>10 or…
- Excel Date Range Between Two Dates: Formulas and ExamplesTo calculate the number of days between two dates in Excel, subtract the earlier date from the later…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.