Duration.Days
Returns the whole number of days in a duration, such as the days between two dates.
Duration.Days(duration as nullable duration) as nullable numberReturns: The whole days as a number, or null when the input is null.
What it does
Subtracting one date from another in M gives a duration, not a number. Duration.Days turns that duration into the whole number of days. It is how you get the days between an invoice date and a payment date, or the days past due. It returns whole days only. A part of a day is dropped, so use Duration.TotalDays when the fraction matters.
Syntax and arguments
- duration: a duration value, usually the result of subtracting two dates or two datetimes.
Example
An accounts receivable team wants to know how many days each invoice is past due as of October 29, 2026. Each due date is subtracted from the report date, and the duration is turned into whole days.
let
ReportDate = #date(2026, 10, 29),
Invoices = #table(type table [Invoice = text, DueDate = date],
{{"INV1043", #date(2026, 9, 15)}, {"INV1044", #date(2026, 10, 20)}}),
Aged = Table.AddColumn(Invoices, "DaysPastDue", each Duration.Days(ReportDate - [DueDate]), Int64.Type),
Bucket = Table.AddColumn(Aged, "Bucket", each
if [DaysPastDue] <= 0 then "Not yet due"
else if [DaysPastDue] <= 30 then "1 to 30"
else "Over 30", type text)
in
Bucket
INV1043 is 44 days past due and falls in the Over 30 bucket. INV1044 is 9 days past due and falls in 1 to 30. A duration subtraction gives the day count without any date arithmetic in the report.
Example: a second, different use
Count the days between two dates in the same month, such as the first and last day of a budget period, with Duration.Days(#date(2026, 10, 29) - #date(2026, 10, 1)), which gives 28.
Things to watch
- A date minus a date is a duration, so the result is not a number until you wrap it in Duration.Days.
- Duration.Days drops the time part. Two datetimes 2 days and 5 hours apart give 2.
- Day counts from a date subtraction do not include the end date, so add one if the count should be inclusive.
- Null in gives null out.
In business terms
Days past due, days sales outstanding, time to approve and the age of an open item are all day counts. Duration.Days is the step that turns a pair of dates into the number that an aging report and a collection policy are based on.