Date.DayOfYear
DateReturns the day of the year (1–366) for the given date.
Syntax
Date.DayOfYear(dateTime as any) as nullable numberParameters
| Name | Type | Required | Description |
|---|---|---|---|
dateTime | any | Yes | A date, datetime, or datetimezone value. |
Return Value
number — An integer from 1 to 366 representing the day of the year.
Remarks
Date.DayOfYear returns an integer from 1 (January 1) to 365 or 366 (December 31, depending on leap year) for a date, datetime, or datetimezone value. This is useful for cross-year comparisons where you want to compare progress through the year without regard to the specific year — for example, to find the same-day-of-year sales across multiple years. If the input is null, the function returns null.
Note that the maximum value is 365 in a common year and 366 in a leap year. When comparing day-of-year values across years, be aware that day 366 only exists in leap years. For these same-period comparisons, also consider using Date.Month + Date.Day directly to avoid the leap year edge case.
Date.DayOfYear is a simple ordinal counter with no locale or timezone sensitivity. It is not related to ISO week numbering; for ISO week numbers use Date.WeekOfYear with Day.Monday.
Examples
Example 1: Add day-of-year number to Sales table
Table.AddColumn(
Table.SelectColumns(
Table.FirstN(Sales, 5),
{"OrderID", "OrderDate"}
),
"DayOfYear", each Date.DayOfYear([OrderDate]), Int64.Type
)OrderID | OrderDate | DayOfYear | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | 15 |
| 2 | 2 | 1/18/2024 | 18 |
| 3 | 3 | 2/1/2024 | 32 |
| 4 | 4 | 2/10/2024 | 41 |
| 5 | 5 | 3/5/2024 | 65 |
Example 2: Day of year for a specific date
#table(
type table [Date = date, DayOfYear = number],
{{#date(2024, 3, 15), Date.DayOfYear(#date(2024, 3, 15))}}
)Date | DayOfYear | |
|---|---|---|
| 1 | 3/15/2024 | 75 |
Example 3: Filter rows to same day-of-year across all years
Table.SelectRows(
Table.AddColumn(Sales, "DayOfYear", each Date.DayOfYear([OrderDate]), Int64.Type),
each [DayOfYear] = 65
)OrderID | CustomerName | Product | Category | UnitPrice | Quantity | OrderDate | Region | DayOfYear | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 5 | Diana | Widget A | Widgets | 25 | 6 | 3/5/2024 | West | 65 |