Date parts and durations
Derive reporting periods and elapsed days from real date values.
~9 min read · beginner · Module 7: Numbers, dates and conditional columns
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Convert dates and numbers with a locale
Date functions need date values. If a column is still text, convert it first, with a culture if it isn't ISO yyyy-mm-dd. Slicing text with Text.Middle to get months breaks the moment a format changes.
Date parts
let
Source = #table({"OrderDate"}, {{#date(2026, 3, 14)}, {#date(2026, 11, 2)}}),
Parts = Table.AddColumn(Source, "Parts", each [
Year = Date.Year([OrderDate]),
Month = Date.MonthName([OrderDate], "en-US"),
Quarter = "Q" & Text.From(Date.QuarterOfYear([OrderDate]))
]),
Expanded = Table.ExpandRecordColumn(Parts, "Parts", {"Year", "Month", "Quarter"})
in
ExpandedResult
| OrderDate | Year | Month | Quarter |
|---|---|---|---|
| 2026-03-14 | 2026 | March | Q1 |
| 2026-11-02 | 2026 | November | Q4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Add Column → Date offers these without typing. Month names follow the culture, so pass one when it matters: Date.MonthName(date, "de-DE") gives "März".
Durations
Subtracting two dates gives a duration. Duration.Days extracts the whole number of days:
let
Source = #table({"Ticket", "Opened", "Closed"}, {
{"T-1", #date(2026, 10, 1), #date(2026, 10, 4)},
{"T-2", #date(2026, 10, 30), #date(2026, 11, 2)},
{"T-3", #date(2026, 10, 31), null}
}),
Days = Table.AddColumn(Source, "Days to close", each if [Closed] = null then null else Duration.Days([Closed] - [Opened]), Int64.Type)
in
Table.SelectColumns(Days, {"Ticket", "Days to close"})Result
| Ticket | Days to close |
|---|---|
| T-1 | 3 |
| T-2 | 3 |
| T-3 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Open tickets have no Closed date, so their duration is null. That's usually what you want: replacing it with 0 would report them as closed on the day they opened. If you need the age of open items, subtract from a fixed report date stored in a parameter rather than from 'now', so the result is reproducible.
Durations can also be added to dates: [Opened] + #duration(7, 0, 0, 0) is a week later. For calendar arithmetic in months or years use Date.AddMonths and Date.AddYears instead, because months don't have a fixed number of days.
Datetimes
Subtracting datetimes gives fractional days. Duration.Days returns the whole-day part; Duration.TotalHours returns the total as a decimal. Choose the unit the report uses, and agree whether a ticket opened at 23:59 and closed at 00:01 took 0 days or 1.
Where to find this in Excel
- Excel for Windows
- Add Column > Date > Year, Month or Quarter; Add Column > Duration > Days
- What you see
- Each command adds a column with that part of the date or duration, and leaves the original column alone.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a column based on a data type (Power Query) (Microsoft Support).
Common mistakes
- Extracting months from date text with Text.Middle instead of converting to a date first.
- Replacing null durations with 0 for open items.
- Using DateTime.LocalNow() for ages, so the same data gives a different answer every day.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Add Column → Date → Year / Month / Quarter / Week / Day, and Add Column → Date → Subtract Days for durations between two date columns.
- Power BI users often build period columns in a separate date table instead of on every fact table; both use the same Date functions.
- The simulator supports DateTime.LocalNow() but graded exercises never use it, because the result changes with the clock.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Add year, month and quarter columns (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~7 minPro
Add year, month and quarter columns (kite shop)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- beginner~7 minPro
Add year, month and quarter columns (bakery)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- beginner~7 minPro
Add year, month and quarter columns (bike rental)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- intermediate~8 minPro
Calculate days to close each ticket (telecom)
Convert Opened and Closed to dates and compute whole days between them; open tickets stay null.
- intermediate~8 minPro
Calculate days to close each ticket (saas)
Convert Opened and Closed to dates and compute whole days between them; open tickets stay null.
- advanced~10 minPro
Calculate age in completed years on a report date (rowing club)
Work out each member's age on 30 June 2026, counting only birthdays that have already happened.
- advanced~10 minPro
Calculate age in completed years on a report date (library cards)
Work out each member's age on 30 June 2026, counting only birthdays that have already happened.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (kite shop)
Label each order with the Monday of its week and count orders per week.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (bakery)
Label each order with the Monday of its week and count orders per week.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (bike rental)
Label each order with the Monday of its week and count orders per week.
- intermediate~7 minPro
Label orders with an April–March fiscal year (kite shop)
The company's fiscal year runs from 1 April to 31 March and is named after the year it ends in.
- intermediate~7 minPro
Label orders with an April–March fiscal year (bakery)
The company's fiscal year runs from 1 April to 31 March and is named after the year it ends in.
- intermediate~7 minPro
Label orders with an April–March fiscal year (bike rental)
The company's fiscal year runs from 1 April to 31 March and is named after the year it ends in.