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.
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:
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:
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:
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.
It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
This tutorial uses a single Excel SEQUENCE function to create a perpetual calendar. Includes sample files and a step-by-step guide.
In this lesson you’ll create a fully functional calendar in Excel. The calendar showcases the new SEQUENCE dynamic array formula.
This article shows you how to calculate the number of working days between two dates in Excel using the NETWORKDAYS function.
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.