There are at least six different week numbering systems currently in use around the world. For example, in the USA, Canada and Australia the week begins on Sunday while the ISO standard week begins on Monday.
This article will show you how to got the week of the month whatever system your country uses using the WEEKNUM and ISOWEEKNUM functions.
- Excel 365
- Excel 2021
- Excel 2019
- Excel 2016
- February 12, 2022
- 22 Comments
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 e-Books.
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:
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Some of the things you will learn
Related Articles
Excel SEQUENCE Function
This tutorial uses a single Excel SEQUENCE function to create a perpetual calendar. Includes sample files and a step-by-step guide.
Excel formula to get working days between two dates excluding holidays
This article shows you how to calculate the number of working days between two dates in Excel using the NETWORKDAYS function.
Calculating Loan Payment Dates
This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.
22 Responses
Hi,
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.
Hi 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.
So 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?
Hi 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.
Hi, 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?
Hi 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)+1
Unfortunately 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.
Great. It works for me.
How 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)
Hi 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
Want to calculate attendance on weekly basis. Week starts from Monday and ends on Friday. How I will get numbers of days student is present in a week
Hi Komal
This is quite easy to do using Excel 365’s SUMPRODUCT dymanic array function. The use of SUMPRODUCT is quite quirky but is explained in depth in the Excel 365 Expert Skills book. It is hard to guess the exact result you are trying to achieve without understanding the structure of your workbook but, in short, you’d begin by creating custom logic arrays to identify the values you wanted to sum and then use one or more SUMPRODUCT functions to sum the rows and columns you had identified.
If you read and understand the dynamic array session in the Excel 365 Expert Skills book and are still unable to solve the problem feel free to e-mail me an I’ll point you in the right direction.
How to know the nth occurrence of a day of a given date is either 1st, 2nd, 3rd, 4th or 5th in a month?
e.g.
29-May-2020 = 5th occurrence of Friday
15-May-2020 = 3rd Occurrence of Friday in May month.
Hi Quadeer,
See my answer to Sally above that includes the answer to your question.
Hi Simon,
I am trying to find out how many weeks in Aug 2020 and weeks should be started from Monday. when i am using above formula its showing week count 6 but as per Aug month it has 5 weeks. Could you please help me.
Hi Shankar
Here are the week numbers for 1-Aug-2020 and 31-Aug-2020 (obtained with the ISOWEEKNUM formula that regards the first day of the week as Monday and treats the first week of the the year containing a Thursday as week number 1). The ISO standard is the most widely used week numbering standard.
Monday 31st August 2020 is correctly the sixth week number occurring in the month.
Your question highlights how important it is to define terminology precisely when designing information systems as “week of the month” is not a carefully enough defined term.
The question you are receiving the correct answer to is:
How many different week numbers could any date during this month fall into?
The correct answer to this question (for Aug 2020) is six.
The question you are asking might be:
How many full weeks (Mon-Sat) were there in August 2020?
The correct answer to this question is four.
Or it might also be:
How many Mondays were there in Aug 2020?
The correct answer to this question is five.
With the above information I’m sure you can re-phrase your requirement “I am trying to find out how many weeks in Aug 2020” to one of the above three questions. I’m sure you’ll then have no difficulty in creating a formula that finds the right answer.
Mike Smart
When the date is 08-01-2021 the result of the formula is -51.
Can you please recheck the formula?
The one is used is:
=WEEKNUM(A1,2)–WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1
Hi Richard
I’ve just tested this with both 1-Aug-21 and 8-Jan-21 in cell A1 (as it isn’t clear which part of the world you are in so your date could have two meanings). 1-Aug-21 returned 1 and 8-Jan-21 returned 2. The formula was:
=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1
I cannot understand how you were able to achieve a result of -51. Try copying and pasting the above formula into our own workbook. Perhaps this will enable you to solve the problem.
Hi Mike,
I am using this formula =WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1
to get week number. but its not working. my 1st start date is 1 March 2021 its starting from Monday, it should show 1 week from 1 to 7mar and 8mar to 14 mar as week 2 and onwards. when i reach to 29th march to 4th April it should show result as 1 week but instead of it its showing 5th week. again if i reach to 5th April to 11 April it should show 1st week but showing 2nd week.
kindly help.
Hi Sandeep
Your formula calculates two numbers:
1/ The week number that the first day of the month falls within. In the case of 1st April 2021 this is week 14 of the year (you have opted for System 1 when the week containing January 1st is considered the first week of the year and is numbered 1. You have also set the week to begin on Monday).
1st Jan 2021 was a Friday so the first three days of 2021 were in Week 1 and then 4th Jan began Week 2.
29 Mar -4 April is thus Week 14 of the year and 5 April to 11 April is Week 15.
To determine the week number of the current month you are taking the week number of the year and subtracting the week number that the 1st of the month was within. In this case the 1-Apr-2021 is in week 14. For this reason 1-4 April 2021 fall in the 1st week of the month and the second week begins on 5th April 2021 so the results you are seeing are correct.
There are many ways of defining the “Week of the month” used throughout the world. Your question highlights how important it is to define terminology precisely when designing information systems as “week of the month” is not a carefully enough defined term.
I’m guessing that your actual requirement is:
“Regard the 1st of each month, and the six days that follow, as “Week 1” and increment the week number every seven days. When the 1st of the next month comes around, reset week numbering to “Week 1 again”.
This is easily done of course, using nothing fancier than a little math and a thorough understanding of Excel date serial numbers (covered extensively in both the Essential Skills and Expert skills books). Perhaps your requirement is different again? Whatever it is you should be able to design a solution using date serial number offsets, perhaps in conjunction with the WEEKNUM function.
I hope that the above information enables you to design the solution that you need.
Hi Sandeep
The formula calculates two numbers:
1/ The week number that the first day of the month falls within. In the case of 1st April 2021 this is week 14 of the year (you have opted for System 1 when the week containing January 1st is considered the first week of the year and is numbered 1. You have also set the week to begin on Monday).
1st Jan 2021 was a Friday so the first three days of 2021 were in Week 1 and then 4th Jan began Week 2.
29 Mar -4 April is thus Week 14 of the year and 5 April to 11 April is Week 15.
To determine the week number of the current month you are taking the week number of the year and subtracting the week number that the 1st of the month was within. In this case the 1-Apr-2021 is in week 14. For this reason 1-4 April 2021 fall in the 1st week of the month and the second week begins on 5th April 2021 so the results you are seeing are correct.
There are many ways of defining the “Week of the month” used throughout the world. Your question highlights how important it is to define terminology precisely when designing information systems as “week of the month” is not a carefully enough defined term.
I’m guessing that your actual requirement is:
“Regard the 1st of each month, and the six days that follow, as “Week 1” and increment the week number every seven days. When the 1st of the next month comes around, reset week numbering to “Week 1 again”.
This is easily done of course, using nothing fancier than a little math and the date serial number (covered extensively in both the Essential Skills and Expert skills books). Perhaps your requirement is different again?
I hope that the above information enables you to design the solution that you need.
I’m getting odd results for May and Aug of 2021. I have a column (A) of Friday dates and I am trying to find which Friday of the month each of these dates represent – 1st friday of the month, 2nd friday of the month, etc. Tried each of these:
=WEEKNUM(A2,1)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1),1)+1
shows May 7 as 2nd week and the other May fridays are one week off
=WEEKNUM(A2,2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1),2)+1
shows May 7 as 2nd week and Aug 6 as 2nd week and other fridays in those months are similarly one week off
Hi Scott
To set Friday as the first day in the week you need to use 15 as the second argument for the weeknum function:
For Friday 7th May 2021
=WEEKNUM(A2,15)
… returns week 19 as Friday 7th May begins Week 19 (as May 2021 began on a Saturday)’
=WEEKNUM(DATE(YEAR(A19),MONTH(A19),1),15)
.. returns week 18 as (on 1st May 2021) the first Friday in May 2021 has not yet arrived.
The sum 18-17 returns 1 making Friday 7th May 2021 as the first Friday in the month of May 2021.
For your requirement you do not need to subtract one because you do not want the days prior to Friday to be regarded as week 1 of the month in question. Given this you should appreciate why:
=WEEKNUM(A19,15)-WEEKNUM(DATE(YEAR(A19),MONTH(A19),1),15)
… will correctly identify 7th May 2021 as being the first Friday in May 2021.