Working with Dates, Times, and Duration Values in M: Calculations, Formatting, and Time Intelligence Foundations

Introduction

Imagine you're handed a dataset of customer orders. Each row has an order date, a ship date, and a delivery date — all stored as plain text strings imported from a legacy system. Your manager wants to know the average fulfillment time in business days, whether orders placed in Q4 outperform Q3, and which customers have gone more than 90 days without a purchase. None of that analysis is possible until you understand how M treats date and time values — not just how to parse them, but how to manipulate, compare, and calculate with them.

Date and time handling is one of those topics that seems simple on the surface ("just extract the year, right?") until you're 40 minutes into debugging why two date columns won't subtract correctly, or why your duration keeps showing up as a decimal number instead of days and hours. The M language has a rich, precise type system for temporal data, and once you understand its logic, these problems dissolve.

By the end of this lesson, you'll be able to parse text into proper date and time values, perform arithmetic with dates and durations, format temporal values for display, and lay the groundwork for time intelligence calculations like fiscal quarters, age calculations, and rolling windows. These are the skills that separate analysts who can answer temporal questions from analysts who have to say "I'd need to think about how to do that."

What you'll learn:

• How M represents the four core temporal types: date, time, datetime, and duration
• How to parse text strings into typed temporal values
• How to perform date arithmetic and work with duration values
• How to extract components (year, month, weekday) and format dates for output
• How to build reusable patterns for time intelligence foundations like quarter calculations and age logic

---

Prerequisites

This lesson assumes you're comfortable writing basic M queries in Power Query — you know what let...in means, you can create a custom column, and you've seen functions like Text.Upper or List.Sum before. You don't need to have worked with dates specifically. If you haven't yet, spend 20 minutes in Power Query's Advanced Editor writing a few simple transformations first.

---

The Four Temporal Types in M

M is a strongly typed language, which means it treats a date, a time, a datetime, and a duration as four completely different things — not interchangeable, not automatically convertible. Understanding the distinction between these types is the single most important concept in this entire lesson.

date represents a calendar day with no time component. Think of it as a point on a wall calendar: June 15, 2024. It has no hours, no minutes, no timezone.

time represents a position within a single day, independent of any specific date. 14:30:00 is a time value. It has no knowledge of what day it belongs to.

datetime is the combination: a specific moment in time, anchored to both a calendar day and a clock reading. June 15, 2024 at 14:30:00 is a datetime.

duration is fundamentally different from the others. It doesn't represent a point in time — it represents a span of time. The difference between two dates, or the result of asking "how long did this process take?", is a duration. Think of it as a measurement, like a ruler for time.

Why does this matter? Because M will throw type errors if you mix them. You cannot subtract two time values and expect a date. You cannot add a date and a datetime. The type system enforces coherence, and understanding it means you'll read error messages correctly instead of guessing at fixes.

---

Creating Temporal Values from Scratch

Before you work with real data, it's worth knowing how to create temporal literals in M directly. This is useful for testing logic, setting reference dates, and building hardcoded thresholds.

let
    SampleDate     = #date(2024, 6, 15),          // June 15, 2024
    SampleTime     = #time(14, 30, 0),             // 2:30:00 PM
    SampleDatetime = #datetime(2024, 6, 15, 14, 30, 0),  // June 15, 2024 2:30 PM
    SampleDuration = #duration(3, 8, 45, 0)        // 3 days, 8 hours, 45 minutes
in
    SampleDuration

The #duration constructor takes four arguments: days, hours, minutes, seconds. So #duration(3, 8, 45, 0) means "3 days, 8 hours, 45 minutes, and 0 seconds." This is not a point in time — it's a length of time.

Important: The # prefix on these constructors is not optional decoration — it's required M syntax for temporal literals. Omitting it will cause an error because M will try to interpret date(...) as a function call.

---

Parsing Text into Temporal Values

In practice, dates almost never arrive in your query already typed correctly. They come in as text strings from CSVs, databases, APIs, or Excel files that have been saved in unfortunate ways. Parsing means converting that raw text into a proper M temporal type.

The primary parsing functions you'll use are:

• Date.FromText("2024-06-15") → date
• DateTime.FromText("2024-06-15T14:30:00") → datetime
• Time.FromText("14:30:00") → time

For standard ISO 8601 formats (the YYYY-MM-DD style that looks like "2024-06-15"), these functions work automatically:

let
    RawDate = "2024-06-15",
    ParsedDate = Date.FromText(RawDate)
in
    ParsedDate
    // Result: date value 6/15/2024

The problem arises with non-standard formats. A U.S. sales system might export dates as "06/15/2024". A European system might export "15.06.2024". A legacy system might give you "20240615". For these cases, you need to specify the format explicitly using the optional Culture or Format parameter.

let
    USDate     = Date.FromText("06/15/2024", [Format="MM/dd/yyyy"]),
    EUDate     = Date.FromText("15.06.2024", [Format="dd.MM.yyyy"]),
    CompactDate = Date.FromText("20240615",   [Format="yyyyMMdd"])
in
    USDate
    // All three produce the same date: June 15, 2024

Tip: The format string uses M for month and d for day — but note that M is uppercase (month) while m (lowercase) means minute. This trips up almost everyone at least once. In date formats, use MM for two-digit month. In time formats, use mm for two-digit minute.

When parsing fails — say, the source data contains a badly formed entry like "N/A" in a date column — M will throw an error at that row. We'll cover error handling patterns in a later lesson, but for now, know that try Date.FromText([OrderDate]) otherwise null is a safe pattern that converts unparseable values to null instead of breaking your entire query.

---

Date Arithmetic: Adding, Subtracting, and Comparing

This is where the temporal type system pays off. Once your values are properly typed, arithmetic becomes elegant.

Subtracting Two Dates to Get a Duration

let
    OrderDate    = #date(2024, 3, 1),
    ShipDate     = #date(2024, 3, 8),
    FulfillmentDuration = ShipDate - OrderDate
in
    FulfillmentDuration
    // Result: duration value of 7 days

Subtracting two date values yields a duration. You cannot use this result directly as a number — it's still a duration type. To get a usable number of days, you need Duration.TotalDays:

let
    OrderDate    = #date(2024, 3, 1),
    ShipDate     = #date(2024, 3, 8),
    FulfillmentDays = Duration.TotalDays(ShipDate - OrderDate)
in
    FulfillmentDays
    // Result: 7 (a number)

The Duration library includes several similar functions:

• Duration.TotalDays(d) — total span expressed as fractional days
• Duration.TotalHours(d) — total span as hours
• Duration.TotalMinutes(d) — total span as minutes
• Duration.TotalSeconds(d) — total span as seconds
• Duration.Days(d) — just the days component (not total)
• Duration.Hours(d) — just the hours component

The distinction between Duration.TotalDays and Duration.Days is subtle but critical. If a duration is 2 days and 6 hours:

• Duration.TotalDays returns 2.25 (total including fractional days)
• Duration.Days returns 2 (only the whole-day component)

Adding a Duration to a Date

To shift a date forward or backward, add a duration:

let
    StartDate   = #date(2024, 1, 15),
    ThirtyDaysLater = StartDate + #duration(30, 0, 0, 0),
    SixtyDaysBefore = StartDate - #duration(60, 0, 0, 0)
in
    ThirtyDaysLater
    // Result: February 14, 2024

Comparing Dates

Comparison operators work exactly as you'd expect:

let
    Date1 = #date(2024, 3, 1),
    Date2 = #date(2024, 6, 15),
    IsAfter = Date2 > Date1,   // true
    IsEqual = Date1 = Date2    // false
in
    IsAfter

This is enormously useful in conditional logic. For example, to flag orders that took longer than 5 days to ship:

// In a custom column:
if Duration.TotalDays([ShipDate] - [OrderDate]) > 5 then "Late" else "On Time"

---

Extracting Date Components

Once you have a properly typed date or datetime, M gives you a clean library of functions to pull out individual components. These are essential for grouping, filtering, and building dimensions.

let
    SampleDate = #date(2024, 9, 17),  // September 17, 2024 — a Tuesday

    YearValue    = Date.Year(SampleDate),          // 2024
    MonthValue   = Date.Month(SampleDate),         // 9
    MonthName    = Date.MonthName(SampleDate),     // "September"
    ShortMonth   = Date.MonthName(SampleDate, "en-US"),  // "September" (locale-aware)
    DayValue     = Date.Day(SampleDate),           // 17
    DayOfWeek    = Date.DayOfWeek(SampleDate),     // 1 (Tuesday; 0 = Monday by default)
    DayName      = Date.DayOfWeekName(SampleDate), // "Tuesday"
    WeekOfYear   = Date.WeekOfYear(SampleDate),    // 38
    QuarterValue = Date.QuarterOfYear(SampleDate)  // 3
in
    QuarterValue

Watch out: Date.DayOfWeek returns a number, but the starting day of the week depends on the optional second argument. By default it uses Monday = 0, but you can pass Day.Sunday as the second argument to get Sunday = 0 behavior, which matches most U.S. business conventions.

For datetime values, you additionally have access to time components:

let
    SampleDT = #datetime(2024, 9, 17, 14, 30, 45),
    HourPart   = DateTime.Time(SampleDT),  // Extracts the time portion as a #time value
    YearPart   = DateTime.Date(SampleDT)   // Extracts the date portion as a #date value
in
    HourPart

---

Formatting Dates for Output

Extracting components is for calculation. Formatting is for display — when you need to present dates in a specific human-readable form, generate report labels, or create keys for joining tables.

The primary function is Date.ToText:

let
    SampleDate = #date(2024, 9, 17),
    
    ISOFormat   = Date.ToText(SampleDate, "yyyy-MM-dd"),    // "2024-09-17"
    USFormat    = Date.ToText(SampleDate, "MM/dd/yyyy"),    // "09/17/2024"
    LongFormat  = Date.ToText(SampleDate, "MMMM d, yyyy"),  // "September 17, 2024"
    MonthYear   = Date.ToText(SampleDate, "MMM yyyy"),      // "Sep 2024"
    FiscalLabel = "FY" & Text.From(Date.Year(SampleDate))   // "FY2024"
in
    LongFormat

The format string syntax follows standard .NET date formatting conventions, which you may recognize from other tools. A few commonly used patterns:

Format Token	Meaning	Example Output
yyyy	4-digit year	2024
yy	2-digit year	24
MMMM	Full month name	September
MMM	Abbreviated month	Sep
MM	2-digit month number	09
dd	2-digit day	17
d	Day without leading zero	17
dddd	Full weekday name	Tuesday

Tip: When you need locale-aware month or day names (for example, a report in Spanish), pass the culture code as a second argument: Date.ToText(SampleDate, "MMMM", "es-ES") returns "septiembre".

---

Time Intelligence Foundations

"Time intelligence" refers to calculations that require reasoning about time: year-over-year comparisons, rolling averages, age calculations, fiscal period assignments, and so on. Power BI users often rely on DAX for this, but building these calculations in Power Query at the data preparation layer makes your model cleaner and more portable.

Calculating Age

Age is the classic temporal calculation that trips people up because naïve subtraction gives you days, not years:

let
    BirthDate = #date(1985, 11, 20),
    Today     = Date.From(DateTime.LocalNow()),
    
    // Naive approach -- gives total days, not age in years
    RawDays = Duration.TotalDays(Today - BirthDate),
    
    // Correct approach: compare year numbers, then adjust if birthday hasn't occurred yet
    YearDiff = Date.Year(Today) - Date.Year(BirthDate),
    BirthdayPassedThisYear = 
        (Date.Month(Today) > Date.Month(BirthDate)) or 
        (Date.Month(Today) = Date.Month(BirthDate) and Date.Day(Today) >= Date.Day(BirthDate)),
    Age = if BirthdayPassedThisYear then YearDiff else YearDiff - 1
in
    Age

DateTime.LocalNow() returns the current datetime from your system clock. We convert it to a date with Date.From() to strip the time component before doing the comparison.

Assigning Fiscal Quarters

Many organizations run on a fiscal year that doesn't start in January. Here's a reusable pattern for a fiscal year that starts in October (common in U.S. government and some corporations):

let
    Source = YourTable,
    AddFiscalYear = Table.AddColumn(Source, "FiscalYear", each 
        if Date.Month([OrderDate]) >= 10 
        then Date.Year([OrderDate]) + 1 
        else Date.Year([OrderDate])
    ),
    AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "FiscalQuarter", each
        let m = Date.Month([OrderDate])
        in if m >= 10 then "Q1"
           else if m >= 7 then "Q4"
           else if m >= 4 then "Q3"
           else "Q2"
    )
in
    AddFiscalQuarter

Finding the Start and End of a Period

A common need is to anchor a date to the beginning or end of its containing month or quarter, which is useful for period-over-period comparisons:

let
    SampleDate    = #date(2024, 9, 17),
    StartOfMonth  = Date.StartOfMonth(SampleDate),   // September 1, 2024
    EndOfMonth    = Date.EndOfMonth(SampleDate),     // September 30, 2024
    StartOfQuarter = Date.StartOfQuarter(SampleDate), // July 1, 2024
    StartOfWeek   = Date.StartOfWeek(SampleDate)     // September 16, 2024 (Monday)
in
    EndOfMonth

These functions are especially powerful when combined with filtering: to show only records from the current month, you'd compare each record's date against Date.StartOfMonth(Date.From(DateTime.LocalNow())).

---

Hands-On Exercise

Open Power Query and create a blank query (in Power BI Desktop: Home → Transform Data → then in the Query Editor, New Source → Blank Query → Advanced Editor).

Paste and run the following query, then work through the challenges below it:

let
    // Sample order data
    OrderData = Table.FromRecords({
        [OrderID = "ORD-001", OrderDate = "2024-01-05", ShipDate = "2024-01-09"],
        [OrderID = "ORD-002", OrderDate = "2024-03-22", ShipDate = "2024-03-28"],
        [OrderID = "ORD-003", OrderDate = "2024-07-14", ShipDate = "2024-07-15"],
        [OrderID = "ORD-004", OrderDate = "2024-10-31", ShipDate = "2024-11-06"],
        [OrderID = "ORD-005", OrderDate = "2024-12-20", ShipDate = "2024-12-27"]
    }),
    
    // Step 1: Parse text dates into proper date types
    ParseDates = Table.TransformColumns(OrderData, {
        {"OrderDate", Date.FromText, type date},
        {"ShipDate",  Date.FromText, type date}
    }),
    
    // Step 2: Add fulfillment days column
    AddFulfillment = Table.AddColumn(ParseDates, "FulfillmentDays", each
        Duration.TotalDays([ShipDate] - [OrderDate]),
        type number
    )
in
    AddFulfillment

Challenges:

1. Add a column called OrderMonth that contains the abbreviated month name and year (e.g., "Jan 2024") using Date.ToText.
2. Add a column called CalendarQuarter that shows "Q1", "Q2", "Q3", or "Q4" based on the OrderDate.
3. Add a column called ShippedLate that shows true if fulfillment took more than 5 days, false otherwise.
4. Add a column called DaysSinceOrder that calculates how many days have elapsed between each OrderDate and today.

---

Common Mistakes & Troubleshooting

"Expression.Error: We cannot convert the value X to type Date" This is the most common error and it almost always means you're trying to use arithmetic on a column that's still text. Use Date.FromText to parse before doing any calculations.

Duration showing as a decimal in your table When you subtract two dates in a custom column, Power Query might display the result as a decimal (like 7.0) rather than a duration. This is usually a column type issue — the column was typed as number. This is fine if you're using Duration.TotalDays (you want a number), but if you want the full duration type, explicitly set the column type.

Date.MonthName returning numbers instead of names Double-check that your source column is actually a date type, not a number type. A date that looks like "44927" in Excel is stored as a serial number — use Date.From(Number.From([Column])) to convert Excel serial dates.

Fiscal quarter logic producing wrong results When mapping calendar months to fiscal quarters, draw out the month-to-quarter mapping on paper first. It's easy to assign a month to the wrong quarter when thinking backwards from a non-January fiscal year start.

DateTime.LocalNow() giving inconsistent results in scheduled refreshes This function returns the time from whatever machine runs the refresh. In Power BI Service, this is a cloud server in a specific timezone. If your data is in a different timezone, you may need to apply an offset using DateTimeZone.SwitchZone or store the current date as a parameter rather than computing it dynamically.

---

Summary & Next Steps

You now have a complete mental model for how M handles time. The key ideas to take away: M's four temporal types are distinct and non-interchangeable; date arithmetic produces duration values that must be converted to numbers with functions like Duration.TotalDays; Date.FromText with an explicit format string is your standard tool for parsing messy source data; and component extraction functions like Date.Year, Date.QuarterOfYear, and Date.MonthName are the building blocks for every time intelligence pattern.

These foundations unlock a significant range of analytical work. For next steps, explore these related topics:

• Date dimension tables in M: Building a complete calendar table from scratch using List.Dates and Table.FromList — a technique that replaces DAX calendar tables for many use cases
• Time zones with datetimezone: M's fifth temporal type, datetimezone, handles UTC offsets and daylight saving time for global datasets
• Conditional period logic: Using the patterns from this lesson to implement rolling 30/60/90-day windows and year-over-year comparison flags
• Error handling for date parsing: Using try...otherwise patterns to handle messy real-world date columns that mix formats or contain nulls

The more time you spend with these patterns in real projects — parsing messy dates, computing durations, flagging late shipments — the more automatic they'll become. Date logic rewards practice more than memorization.