If you want to find out which week of the year a date falls in, the Date.WeekOfYear function gives you that number. In this article, I’ll show you how to use it, including how to set which day starts the week.
Syntax of Date.WeekOfYear Function
Date.WeekOfYear(dateTime as any, optional firstDayOfWeek as nullable number) as nullable number
where
dateTime(required, any). The date value you want the week number for.firstDayOfWeek(optional, nullable number). ADay.Typevalue such asDay.SundayorDay.Mondaythat sets which day starts the week. Leave it out and Power Query uses a culture-dependent default.
Returns: a number from 1 to 54 showing which week of the year the date falls in. Returns null if dateTime is null.
In plain terms, you hand it a date and it tells you the week number for that date within its year.
Example 1: Get the week number for a date
Find out which week of the year January 15, 2024 falls in.
Date.WeekOfYear(#date(2024,1,15))
Result: 3
With no firstDayOfWeek set, Power Query falls back to its default, and that date lands in week 3.
Example 2: Set Monday as the start of the week
The week number can change depending on which day you treat as the start of the week.
Pass Day.Monday as the second argument to count weeks from Monday.
Date.WeekOfYear(#date(2024,1,7),Day.Monday)
Result: 1
January 7, 2024 was a Sunday, so with Monday-start weeks it still belongs to week 1.
Example 3: Add a week-number column to a table
A common job is tagging each row with its week number so you can group or filter by week later on.
This works the same way as adding a weekday-number column, just with the week of the year instead.
Say you have a Tickets query with a TicketID and an OpenedDate column.
Here is the starting data:
| TicketID | OpenedDate |
|---|---|
| T-2041 | 2024-02-06 |
| T-2042 | 2024-03-21 |
| T-2043 | 2024-05-09 |
| T-2044 | 2024-08-15 |
Set the column to the date type, then add a Week column with Date.WeekOfYear:
let
Source = Excel.CurrentWorkbook(){[Name="Tickets"]}[Content],
Typed = Table.TransformColumnTypes(Source,{{"OpenedDate",type date}}),
AddWeek = Table.AddColumn(Typed,"Week",each Date.WeekOfYear([OpenedDate],Day.Monday),Int64.Type),
Result = Table.SelectColumns(AddWeek,{"TicketID","Week"})
in
Result
Each ticket now carries the week its date falls in, counting from Monday.
The result keeps:
| TicketID | Week |
|---|---|
| T-2041 | 6 |
| T-2042 | 12 |
| T-2043 | 19 |
| T-2044 | 33 |
The Int64.Type on Table.AddColumn types the new column as a whole number straight away, so you do not have to convert it later.
Example 4: Watch the end-of-year boundary
Late-December dates can land in a high week number even when they are close to the next year.
Check December 29, 2024 with Monday as the week start.
Date.WeekOfYear(#date(2024,12,29),Day.Monday)
Result: 52
The function counts weeks within the same calendar year, so this date stays in week 52 of 2024 rather than rolling into the next year. If you need the last day of that period instead, Date.EndOfMonth handles month boundaries.
Things to keep in mind with Date.WeekOfYear
- The week number is not the ISO week number.
Date.WeekOfYearcounts simple calendar weeks from the start of the year, so it can run up to 54 and does not follow the ISO 8601 rules. For ISO weeks you need a custom calculation. - The default
firstDayOfWeekdepends on the locale. Omit the argument and the same date can return a different week number on a machine with a different culture. PassDay.MondayorDay.Sundayexplicitly when you need a stable result. - It expects a date or datetime, not text. A text value throws
Expression.Error: We cannot convert a value of type Text to type Date.Set the column type first, as in Example 3, or convert the text to a date before passing it in. - A
nulldate returnsnull, not an error. When you add the column over a table with blank dates, those rows getnullin theWeekcolumn rather than failing the refresh.
Common questions about Date.WeekOfYear
How is Date.WeekOfYear different from Date.WeekOfMonth?
Date.WeekOfYear returns the week position within the whole year, while Date.WeekOfMonth returns the week position within that date’s month.
Can I get an ISO 8601 week number from it?
Not directly. Date.WeekOfYear uses simple calendar weeks, so an ISO week number needs its own formula rather than this function.
List of All Power Query Functions
Related Power Query Functions / Articles:
