Power Query Date Functions: A Practical Guide

Power Query date functions are M-language functions for converting, extracting, comparing, and adjusting dates and times as data is imported or refreshed. Common choices include Date.From to convert a value to a date, Date.Year to get its year, and Date.AddDays or Date.AddMonths to shift it.

What Power Query date functions do

Power Query is Microsoft’s data transformation tool in Excel and Power BI. Its formulas use the M language, which is different from Excel worksheet formulas: a Power Query expression is evaluated for each row or step in a query, before the resulting data is loaded. Date functions help standardize incoming date values and create useful fields for reporting.

The first question to ask is what kind of value the column contains. Power Query distinguishes among date, datetime, datetimezone, time, and duration. A date represents a calendar day; a datetime also includes a clock time; a datetimezone includes an offset from UTC. A duration is an elapsed interval. Functions designed for one type may not accept another without a conversion.

In Excel for Microsoft 365 on Windows, open the Power Query Editor by selecting a table or range and choosing Data > From Table/Range, or by opening an existing query through Data > Queries & Connections. The Add Column and Transform ribbon tabs provide many date operations when a date-typed column is selected. For a custom operation, Add Column > Custom Column accepts an M expression.

Convert values and set the correct type

Imported dates often arrive as text, especially when data comes from a CSV, website, or system with a different regional format. Date.From converts a compatible value to a date. For example, with a column named OrderTimestamp containing datetime values, Date.From([OrderTimestamp]) returns the date portion and removes the time. For a datetime value, DateTime.Date([OrderTimestamp]) is another direct way to extract that date.

Text conversion needs more care. A string such as 04/05/2026 could mean April 5 or May 4, depending on the source convention. Specify a format and culture when the text’s pattern is known. With a text column named DateText containing day/month/year values, this M expression requests that interpretation:

Date.FromText([DateText], [Format = "dd/MM/yyyy", Culture = "en-GB"])

The format describes the text; it does not change the source data. If the value cannot be interpreted using the stated pattern, conversion can return an error. In the interface, the Change Type > Using Locale option is useful for choosing a type and regional convention together. After conversion, check the column’s type icon and inspect several values, including dates near the start of a month where day/month confusion is easiest to spot.

Extract year, month, day, and weekday

Once a column is a date, component functions return numbers that are useful for grouping and filtering. With dates in a column named OrderDate, Date.Year([OrderDate]) returns the year, Date.Month([OrderDate]) returns a month number from 1 through 12, and Date.Day([OrderDate]) returns the day of the month. These are numeric results, not formatted labels.

For weekday analysis, Date.DayOfWeek([OrderDate], Day.Monday) returns a number where Monday is zero and Sunday is six. Setting the first day explicitly matters: if it is omitted, the function uses Sunday as its default. Date.DayOfWeekName([OrderDate], "en-US") returns a localized weekday name, such as Monday. Names are convenient for display, while numeric values are usually more reliable for sorting and calculations.

To create a year-month label, first decide whether it should be text for display or a date value for analysis. A text label can be created with Date.ToText([OrderDate], [Format = "yyyy-MM", Culture = "en-US"]). It produces a label such as 2026-04, but it is text rather than a date. Keep the original date or a separate month-start date when you need chronological sorting, date relationships, or time-based calculations.

Shift dates and find period boundaries

Use the add functions to move a date by a specified interval. For example, with OrderDate typed as date, Date.AddDays([OrderDate], 7) returns the date one week later, and Date.AddMonths([OrderDate], 1) moves it forward by one month. Related functions include Date.AddWeeks, Date.AddQuarters, and Date.AddYears. The number can be negative to move backward.

To group records into calendar periods, use boundary functions such as Date.StartOfMonth([OrderDate]) and Date.EndOfMonth([OrderDate]). For a quarter or year, the corresponding functions include Date.StartOfQuarter, Date.EndOfQuarter, Date.StartOfYear, and Date.EndOfYear. With an order date of April 18, 2026, the month boundaries are April 1 and April 30, 2026. A month-start column is often a better grouping key than a month name because it remains a date and sorts naturally across years.

For weeks, Date.StartOfWeek can take a first-day setting such as Day.Monday. Be deliberate about the organization’s calendar: a week beginning Sunday is not the same reporting period as one beginning Monday. Likewise, week numbering has regional and business conventions. Use Date.IsoWeekOfYear when the ISO week-numbering system is intended; do not assume a generic week number automatically matches a company’s fiscal calendar.

Work with datetimes, time zones, and durations

When a timestamp includes a time, use the DateTime family for timestamp-specific operations. DateTime.Date extracts its calendar date, while DateTime.Time extracts its time component. Keeping a timestamp as datetime is important when records on the same day must still be ordered or compared by time.

Time-zone handling deserves special attention. A datetime value has no time-zone offset, so it cannot by itself state whether 9:00 means local time or UTC. A datetimezone value carries an offset. Power Query includes functions such as DateTimeZone.SwitchZone for changing an offset and DateTimeZone.RemoveZone for removing zone information; removing the zone does not convert a timestamp into a universally comparable instant. Decide which time zone the business process uses before dropping or changing offsets.

Current-time functions can also behave differently by refresh environment. DateTime.LocalNow() returns a current datetime, but the local environment used by a desktop refresh may differ from the environment used by an online service. If a query depends on “today,” test it in the place where it will refresh and make the intended time zone explicit when the result affects reporting deadlines. For a fixed value during a query evaluation, Power Query also provides fixed current-time functions, including DateTime.FixedLocalNow().

To calculate elapsed days between two date columns, subtract one date from the other to produce a duration, then use Duration.Days. With StartDate and EndDate containing January 1 and January 10, respectively, Duration.Days([EndDate] - [StartDate]) returns 9 elapsed days. If the business rule counts both the first and last dates, add one to that result; inclusive counting is a reporting rule, not a different meaning of elapsed duration.

Build calendar and fiscal reporting fields

Date functions can create standard calendar attributes such as year, month number, month start, quarter, and weekday. These fields help group facts without repeatedly parsing a text label in a report. For a large model or a report with several date relationships, a dedicated calendar table can be more useful than adding every attribute only to a transaction table. It can hold one row per date and include consistent labels, period keys, and business-day flags.

Fiscal calendars need explicit rules. A fiscal year that starts in July, a 4-4-5 retail calendar, or a company-specific week definition cannot always be represented by simply extracting the calendar year or quarter. One approach is to create a date table and calculate fiscal attributes from the organization’s start month or a maintained mapping table. For irregular calendars, a mapping table is often easier to audit than a long collection of nested conditions.

For a simple fiscal year starting in July, the fiscal year label can be derived by assigning dates from July through December to the following year and dates from January through June to the current year. Before publishing such a field, agree whether “FY2027” means the year in which the fiscal year starts or the year in which it ends. That naming convention is not determined by Power Query.

Common errors and practical safeguards

Many date problems are really type or source-data problems. A column that looks like a date may still be text; a conversion can fail because some rows contain blanks, invalid values, or a different pattern. Review errors rather than immediately replacing them with an arbitrary date, since an invented value can silently distort totals and date ranges.

Set the type deliberately. Confirm that date columns show the date type rather than text or datetime when a time is not needed.
Use locale-aware conversion for ambiguous text, and verify the source system’s date convention.
Handle nulls intentionally. A blank date is not the same as a date of zero or a valid period boundary.
Keep display formatting separate from the underlying type. Formatting a date as text may make it harder to sort, relate, or calculate.
Check week starts, fiscal-year labels, and inclusive-versus-exclusive duration rules with the people who use the report.

Another frequent misconception is that a date label such as “Apr” uniquely identifies a month. It does not distinguish April in one year from April in another. Keep a year-month key or a real month-start date for grouping. Also remember that a conversion step can affect later steps: if a column is changed from datetime to date, its time component is discarded from that point onward.

Choosing between the interface and M expressions

The ribbon is practical for common transformations: selecting a date column and using the Date options can add a year, month, quarter, or other component. It also records the operation as a query step, which makes the transformation visible and repeatable on refresh. The exact available choices depend on the selected column’s type and the Power Query host.

Custom Column expressions are useful when a built-in menu does not express the rule, when a culture must be stated, or when several operations belong in one calculation. For example, Date.StartOfMonth([OrderDate]) is a compact M expression for a month key. Use the function names and argument types precisely; M functions are not Excel worksheet functions, and an expression intended for a worksheet cell cannot simply be pasted into the Power Query Editor.

In Excel for the web, Power Query availability and editing capabilities can depend on the connector and Microsoft 365 environment. The Windows desktop steps above describe Excel for Microsoft 365 on Windows. Excel for Mac has Power Query support, but connector and authoring capabilities can differ from Windows; check the commands available in the installed Mac version rather than assuming every Windows connector or ribbon option is present.

Frequently Asked Questions

What is the main Power Query function for dates?
Date.From is a common starting point because it converts a compatible value to a date. The best function depends on the task: use Date.Year to extract a year, Date.AddDays to shift a date, or Date.StartOfMonth to create a month key.

How do I convert text to a date without swapping the day and month?
Use Date.FromText with a known format and culture, or choose Change Type > Using Locale in the Power Query interface. Then inspect sample rows to confirm that the source convention was interpreted correctly.

How do I get the month name in Power Query?
Use Date.MonthName([OrderDate], "en-US") in a custom column to return a localized name. Keep a numeric month or month-start date as well if the result must sort chronologically rather than alphabetically.

How do I calculate the number of days between two dates?
Subtract the earlier date from the later date and pass the resulting duration to Duration.Days. Decide whether the calculation is elapsed days or inclusive calendar-day counting; the latter generally adds one when both endpoints count.

Why does a date function return an error?
The column may be text, contain an invalid or blank value, or use a date pattern that conflicts with the chosen locale. Check the column type and the failing row, then use an explicit conversion rule instead of relying on an ambiguous automatic interpretation.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.