Downloads
Power Query MDateBeginner

Date.DayOfYear

Returns the position of a date in its year, from 1 on January 1 to 365 or 366 on December 31.

Syntax
Date.DayOfYear(dateTime as any) as nullable number

Returns: The day number as a number, or null when the input is null.

What it does

Date.DayOfYear counts the days from the start of the calendar year. January 1 is 1, and December 31 is 365, or 366 in a leap year. It is handy for year to date logic and for a running count of days elapsed, and it is the simplest way to say how far through the year a date falls.

Syntax and arguments

Example

A controller wants to know how far through the year a reporting date is, so that year to date spending can be compared with the share of the year that has passed.

let
    ReportDate = #date(2026, 10, 29),
    DayNumber = Date.DayOfYear(ReportDate),
    ShareOfYear = DayNumber / 365,
    Percent = Number.Round(ShareOfYear * 100, 0)
in
    {DayNumber, Percent}

October 29, 2026 is day 302 of the year. The share of the year that has passed is about 83 percent, which the rounded result shows as 83. Spending that is well above 83 percent of budget by this date is an early warning.

Example: a second, different use

Use the day number to compare the same point in a leap year and a non-leap year, where the day numbers shift after February.

Things to watch

In business terms

Year to date budget tests, straight line depreciation and run rate forecasts all compare progress through the year. The day of the year gives a clean, comparable measure of time elapsed.

Related functions

All Power Query M functions · View as Markdown · M basics