Excel WEEKNUM function – convert week number to date and vice versa

Excel's WEEKNUM function: Your guide to week number calculations

While Excel offers numerous functions for dates, the WEEKNUM function stands alone for week number calculations. This tutorial explores its syntax, arguments, and practical applications, demonstrating how to extract week numbers from dates, convert week numbers back to dates, and perform other related calculations.

Understanding the WEEKNUM Function

The WEEKNUM function returns the week number (1-54) of a given date within a year. Its syntax is:

WEEKNUM(serial_number, [return_type])

• serial_number: A date (cell reference, DATE function result, or a formula output).
• return_type (optional): Specifies the starting day of the week (default is 1, Sunday).

The table below lists supported return_type values:

return_type	Week Begins On
1, 17 (or omitted)	Sunday
2, 11	Monday
12	Tuesday
13	Wednesday
14	Thursday
15	Friday
16	Saturday
21	Monday (ISO 8601 system)

Week Numbering Systems:

• System 1: The week containing January 1st is week 1 (default).
• System 2 (ISO 8601): Week 1 contains the year's first Thursday; Monday is the start of the week. return_type 21 is used.

Note: Excel 2007 and earlier only support return_type 1 and 2.

Date to Week Number:

The simplest usage: =WEEKNUM(A2) (A2 contains the date; default Sunday start). To start on Monday: =WEEKNUM(A2, 2). You can also directly input dates using DATE(year, month, day), e.g., =WEEKNUM(DATE(2025, 5, 15), 2).

Week Number to Date (ISO 8601):

Excel lacks a direct week number to date conversion. The following formulas (assuming year in A2, week number in B2) calculate the start and end dates using the ISO 8601 system (Monday start):

• Start Date: =DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) B2 * 7
• End Date: =DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) B2 * 7 6 or =D2 6 (where D2 is the start date)

Remember to format the cells as dates. These formulas leverage the fact that the first Monday of the year is between December 29th and January 4th.

Alternative formulas for different week numbering systems are provided in the original article.

Month from Week Number:

Combine MONTH and the week number to date conversion: =MONTH(DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) B2 * 7) (ISO system).

Week Number within a Month:

Calculate the week number within the month using WEEKNUM and DATE:

• Monday start: =WEEKNUM($A2,21)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1),21) 1
• Sunday start: =WEEKNUM($A2)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1)) 1

Summing and Averaging by Week Number:

Use SUMIF and AVERAGEIF with a helper column containing the week numbers calculated using WEEKNUM.

Conditional Formatting:

Highlight cells based on week number using a conditional formatting rule: =WEEKNUM($A2)=10 (highlights cells where week number is 10).

This comprehensive guide empowers you to effectively utilize Excel's WEEKNUM function for various date-related calculations. Remember to choose the appropriate formulas based on your specific week numbering system requirements.

The above is the detailed content of Excel WEEKNUM function – convert week number to date and vice versa. For more information, please follow other related articles on the PHP Chinese website!