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.
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:
If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date, there are several options depending on your needs. Do you just want to display the day name? If you only want to display a day name, you don't need a formula – you can use a...
If you need to extract the month from a date, you can use the MONTH function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date. Here's how the formula works: The MONTH function takes just one...
If you need to extract the year from a date, you can use the YEAR function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date. How the formula works The YEAR function takes just one argument,...
The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. By default, weeks begin on Sunday.
The Excel ISOWEEKNUM function takes a date and returns a week number (1-54) that follows ISO standards, where weeks begin on Monday and week number 1 is assigned to the first week in a year that contains a Thursday.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.