Get week number from date

Get week number from date

Generic formula 

=WEEKNUM(date)

Related formulas 

Get day name from date

Get month from date

Get year from date

Explanation

If you need to get the week number from the day from a date, you can use the WEEKNUM function. The date must be in a form that Excel recognizes as a valid date.

How this formula works:

The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate the week number. See the WEEKNUM page for more information.

In the example, the formula is:

=WEEKNUM(B5)

B4 contains a date value for January 5, 2016. The DAY function returns the number 2 representing the calculated week number. 

Note that by default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1).

With a return_type of 2, week 1 begins on January 1, and week 2 begins on the next Monday.

ISO week number

With ISO week numbers, week 1 starts on the Monday of the first week in a year with a Thursday. This means that the first day of the year for ISO weeks is always a Monday in the period between Jan 29 and Jan 4.

Starting with Excel 2010 for Windows and Excel 2011 for Mac, you can generate an ISO week number using 21 as the return_type:

=WEEKNUM(date,21)

In Excel 2013, there is a new function called ISOWEEKNUM.

For more details, see Ron de Bruin's nice write-up on Excel week numbers.

Get week number from date

Generic formula 

=WEEKNUM(date)

Related formulas 

Get day name from date

Get month from date

Get year from date

Explanation

If you need to get the week number from the day from a date, you can use the WEEKNUM function. The date must be in a form that Excel recognizes as a valid date.

How this formula works:

The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate the week number. See the WEEKNUM page for more information.

In the example, the formula is:

=WEEKNUM(B5)

B4 contains a date value for January 5, 2016. The DAY function returns the number 2 representing the calculated week number. 

Note that by default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1).

With a return_type of 2, week 1 begins on January 1, and week 2 begins on the next Monday.

ISO week number

With ISO week numbers, week 1 starts on the Monday of the first week in a year with a Thursday. This means that the first day of the year for ISO weeks is always a Monday in the period between Jan 29 and Jan 4.

Starting with Excel 2010 for Windows and Excel 2011 for Mac, you can generate an ISO week number using 21 as the return_type:

=WEEKNUM(date,21)

In Excel 2013, there is a new function called ISOWEEKNUM.

For more details, see Ron de Bruin's nice write-up on Excel week numbers.