Power Query date functions cheat sheet (tested)
Cheat sheet · By Tahir Nas · Examples run in our simulator · Published · Updated
Every row runs on d = #date(2026, 3, 14), a Saturday, unless the expression says otherwise. Dates are shown in ISO form.
let
d = #date(2026, 3, 14),
Rows = {
{"Date.Year(d)", Text.From(Date.Year(d))},
{"Date.Month(d)", Text.From(Date.Month(d))},
{"Date.Day(d)", Text.From(Date.Day(d))},
{"Date.MonthName(d, ""en-GB"")", Date.MonthName(d, "en-GB")},
{"Date.DayOfWeekName(d, ""en-GB"")", Date.DayOfWeekName(d, "en-GB")},
{"Date.DayOfWeek(d, Day.Monday)", Text.From(Date.DayOfWeek(d, Day.Monday))},
{"Date.QuarterOfYear(d)", Text.From(Date.QuarterOfYear(d))},
{"Date.DayOfYear(d)", Text.From(Date.DayOfYear(d))},
{"Date.StartOfMonth(d)", Date.ToText(Date.StartOfMonth(d), [Format = "yyyy-MM-dd"])},
{"Date.EndOfMonth(d)", Date.ToText(Date.EndOfMonth(d), [Format = "yyyy-MM-dd"])},
{"Date.StartOfWeek(d, Day.Monday)", Date.ToText(Date.StartOfWeek(d, Day.Monday), [Format = "yyyy-MM-dd"])},
{"Date.StartOfQuarter(d)", Date.ToText(Date.StartOfQuarter(d), [Format = "yyyy-MM-dd"])},
{"Date.AddMonths(d, 1)", Date.ToText(Date.AddMonths(d, 1), [Format = "yyyy-MM-dd"])},
{"Date.AddDays(d, -14)", Date.ToText(Date.AddDays(d, -14), [Format = "yyyy-MM-dd"])},
{"Date.DaysInMonth(d)", Text.From(Date.DaysInMonth(d))},
{"Date.IsLeapYear(d)", Text.From(Date.IsLeapYear(d))},
{"Duration.Days(#date(2026, 4, 1) - d)", Text.From(Duration.Days(#date(2026, 4, 1) - d))},
{"Date.ToText(d, [Format = ""dd MMM yyyy"", Culture = ""en-GB""])", Date.ToText(d, [Format = "dd MMM yyyy", Culture = "en-GB"])}
}
in
#table({"Expression", "Result"}, Rows)Result
| Expression | Result |
|---|---|
| Date.Year(d) | 2026 |
| Date.Month(d) | 3 |
| Date.Day(d) | 14 |
| Date.MonthName(d, "en-GB") | March |
| Date.DayOfWeekName(d, "en-GB") | Saturday |
| Date.DayOfWeek(d, Day.Monday) | 5 |
| Date.QuarterOfYear(d) | 1 |
| Date.DayOfYear(d) | 73 |
| Date.StartOfMonth(d) | 2026-03-01 |
| Date.EndOfMonth(d) | 2026-03-31 |
| Date.StartOfWeek(d, Day.Monday) | 2026-03-09 |
| Date.StartOfQuarter(d) | 2026-01-01 |
| Date.AddMonths(d, 1) | 2026-04-14 |
| Date.AddDays(d, -14) | 2026-02-28 |
| Date.DaysInMonth(d) | 31 |
| Date.IsLeapYear(d) | false |
| Duration.Days(#date(2026, 4, 1) - d) | 18 |
| Date.ToText(d, [Format = "dd MMM yyyy", Culture = "en-GB"]) | 14 Mar 2026 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Things that trip people up
- Date.DayOfWeek counts from 0 and depends on the first day you pass: with Day.Monday, Monday is 0 and Saturday is 5. Without the argument, the culture decides.
- Format strings are case-sensitive:
MMis month,mmis minutes,yyyyis the year. - Subtracting dates gives a duration; use Duration.Days to get a number.
- Date.AddMonths clamps to the end of shorter months: 31 January plus one month is 28 February (29 in a leap year).
- Week numbers (Date.WeekOfYear) aren't ISO 8601 week numbers; check the definition your organisation uses before relying on them.
Start-of-period functions are the backbone of reporting periods: group on Date.StartOfMonth or Date.StartOfWeek and the result is a real date that sorts and joins correctly. End-of-period functions are what due-date and accrual rules usually need, such as 'ten days after month end'.
Datetimes and text
DateTime.Date drops the time from a datetime; DateTime.Time keeps only the time. To read dates from text, convert with a culture (Date.FromText(text, [Culture = "en-GB"])) rather than splitting the text yourself.
Choosing the culture
Month and day names, and the default first day of the week, come from the culture. Pass one explicitly in shared queries ("en-GB", "de-DE") so the output doesn't change with the machine running the refresh. The same date gives "March" in en-GB and "März" in de-DE, and Date.DayOfWeek without a first-day argument can number Saturday differently in different cultures.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
- beginner~7 minPro
Add year, month and quarter columns (kite shop)
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 reimbursement dates (consultancy)
Claims are paid 10 days after the end of the month they were made in. Add Pay Date.
Related pages
Lessons
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Date functions (Concept, Microsoft Learn)
- Data types in Power Query (Concept, Microsoft Learn)
- Date.Year (Function reference, Microsoft Learn)
- Text.From (Function reference, Microsoft Learn)
- Date.Month (Function reference, Microsoft Learn)
- Date.Day (Function reference, Microsoft Learn)
- Date.MonthName (Function reference, Microsoft Learn)
- Date.DayOfWeekName (Function reference, Microsoft Learn)
- Date.DayOfWeek (Function reference, Microsoft Learn)
- Date.QuarterOfYear (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.