It’s not unusual to want to split data into weeks. Excel recognizes this and provides the **WEEKNUM** function, which converts a date into a number between 1 and 52, representing which week of the year the date falls into.

If you’re unfamiliar with Excel formulas and functions, you could benefit greatly from our completely free Basic Skills e-book, which explains the basics of Excel formulas.

Sometimes you want to extract weeks within months instead of years. For example you want to identify which of your January sales fell into the first, second, third and fourth week of the month. This is much more of a challenge, as Excel doesn’t provide a built-in function that allows you to do this.

## How the WEEKNUM function works

It’s important to understand how WEEKNUM works before you can use it to extract the week of month from a date.

WEEKNUM accepts 2 arguments: a date and a rather cryptic *Return_type*.

The *Return_type* controls which day the function will treat as the first day of the week. If you set this to **1** Excel will treat Sunday as the first day of the week. If you set it to **2** Excel will treat Monday as the first day of the week.

There are also 7 other options for different week starts. Clicking *Help on this function* will display a help file showing all of them. You can also see more about getting help in the free Basic Skills e-book.

## Extracting the week of the month

The formula to extract the week of month from a date is:

**=WEEKNUM(A1,2)–WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1**

The formula works as follows:

- Extract the ‘week of year’ from the date.
- Extract the ‘week of year’ from the date of the first day of the month the date falls within.
- Subtract the two week numbers and add one (so that the first week does not appear as zero).

You can control which day is treated as the first day of the week by changing the second argument in **both** of the WEEKNUM functions:

=WEEKNUM(A1,**2**)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),**2**)+1

The DATE, YEAR and MONTH functions are explained in depth in our Expert Skills Books and Ebooks.

## The ISOWEEKNUM function

ISOWEEKNUM is an alternative function that uses the ISO definition of week numbering. This treats Monday as the first day of the week and treats the first week of the year containing a Thursday as week number 1.

ISOWEEKNUM can be used instead of the WEEKNUM function:

**=ISOWEEKNUM(A1)-ISOWEEKNUM(DATE(YEAR(A1),MONTH(A1),1))+1**

## A working example

You can download an example workbook showing the formula in action:

Manoj KaushikHi,

I need to know how to execute the below condition in excel through formulas mentioned in this forum-

E.g. 29th Oct 2018 to 30th Oct 2018 should fall in first week of November instead of Week 5 of October. In other words, my Week is starting on Saturday, So Oct 2018 will have 4 weeks only and date 29th Oct to 2nd Nov, 2018 should fall in Week 1 of November. The formula I am using as of now is as below –

=IF(K89=”No Date”,”No Week”,CONCAT(“Week “,WEEKNUM(I89-1)-WEEKNUM(I89-DAY(I89)-6)))

Please help.

Simon SmartHi Manoj,

The WEEKNUM function has an optional argument that lets you specify which day should be treated as the start of the week. To tell WEEKNUM to use Saturday as the first day of the week, use a formula like:

=WEEKNUM(I89,16)The ‘magic number’ 16 indicates that Saturday should be the start of the week, but you can see a list of all of the week start options in Excel’s help files.

JacobSo I have been trying to use the function to track two rolling week dates in excel, and the issue is that i cant get it to work consistently.

I am tracking Mondays, so using the 2 code.

The issue is that i need it to accurately roll from identifying the week span of wk5-wk1 to wk1-wk2 and so on. But because of how the week dates are spread apart if i set the spacing so that the mondays get tracked it will either show wk4-wk1 to wk1-wk2 or it will do wk5-wk1 to wk2-wk2.

Can you help me fix this?

Simon SmartHi Jacob,

I’m afraid I’m having some trouble understanding your question, but it might help to know that there are two different algorithms that the WEEKNUM function can use. If you use 21 instead of 2 the function will use the alternate system of week numbers. You can find more information in the help file.

AbhishekHi, when I am calculating the week number of 09/27/2019, using =WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1 then the result shows 5. But as per the Indian calendar, it should be 4. How can i get it?

Simon SmartHi Abhishek,

The second parameter of the WEEKNUM function offers the choice of two different systems of week numbering. The formula shown above uses system 1, which treats the week containing the 1st of January as the first week of the year.

My guess is that the Indian calendar may use system 2, which treats the week containing the first Thursday of the year as the first week of the year.

You can easily modify the formula to use system 2 by changing it to:

=WEEKNUM(A1,

21)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),21)+1Unfortunately this still seems to return a 5 for the date you mentioned, so my guess is that you may need one of the other options. There are a total of 10 different options that you can use to control how the week number is calculated and you can find them all listed on Microsoft’s help page for the WEEKNUM function.

Sally WrightHow do I create a formula that will calculate a monthly date? I need the formula to work so that the date will always be the 2nd week of the month on a Thursday throughout the year (and the next year etc)

Mike SmartHi Sally

Our Book “Excel 2019 Construction Kit #1: Calendar and Year Planner” contains many advanced date/time calculations (such as calculating the current phase of the moon from a date).

One of the problems solved in the book is to calculate the dates of public holidays. A common format for a public holiday is:

The [Nth] [Day Name] in [Month Name]

For example, Martin Luther King day is the third Monday in January.

Finding dates such as the second Thursday in January is very simple for humans to understand but is a little more challenging for Excel. The problem arises because months have a variable number of days and can begin on a different day each year.

Session 4 of the book suggests three different ways to solve this problem.

1. By combining the DATE, WEEKDAY and MOD functions.

2. By combining the DATE, CHOOSE and WEEKDAY functions.

3. A novel generic solution that uses a simple four argument function call to return the correct answer.

The first two methods are quite complex but the last makes this type of calculation very simple. The book explains in simple terms how each method can be implemented and how the functions work but, since you asked, here are the formulas (in the context of Martin Luthor King day which falls on the third Monday in January).

Solution 1. =DATE(CalendarYear,1,15)+ MOD(7-WEEKDAY(DATE(CalendarYear,1,15),3),7)

Solution 2. =DATE(CalendarYear,1,15)+CHOOSE(7-WEEKDAY(DATE(CalendarYear,1,15),3),1,2,3,4,5,6,0)

The named range CalendarYear in the example above is the year under consideration.

For Solution 3 you’d have to study Session 4 in the above book but it enables the same result with a very simple function call by moving much of the logic to a separate table (without any use of macros or VBA code).

Best Regards

Mike Smart