Duration.TotalDays Function in Power Query M (4 Examples)

You want the full length of a duration as a single number of days, decimals and all. Duration.TotalDays does exactly that. It turns a duration into a decimal day count, and the hours, minutes, and seconds all get counted in.

Table of Contents

Syntax of Duration.TotalDays Function

Duration.TotalDays(duration as nullable duration) as nullable number

where:

duration (required, nullable duration): The duration value you want to express as a total number of days. You can pass a literal built with #duration, or any expression that produces a duration, such as the difference between two datetimes.

Returns: A number giving the total days in the duration, including the fractional part from hours, minutes, and seconds. If the input is null, the result is null.

In plain terms, a 12-hour duration comes back as 0.5, not as zero days.

Example 1: Convert a Two-Day Duration to Days

Here you have a duration of 2 days and 12 hours. You want it as a single day count.

let
    Source = Duration.TotalDays(#duration(2,12,0,0))
in
    Source

Result: 2.5

The 2 whole days stay as 2, and the 12 hours add another 0.5. Together that gives 2.5 days.

Example 2: Express Hours and Minutes as a Fraction of a Day

This duration has no whole days at all. It is just 6 hours and 30 minutes.

let
    Source = Duration.TotalDays(#duration(0,6,30,0))
in
    Source

Result: 0.2708333333333333

There are no full days here, so the answer comes in below 1. That long decimal is just 6.5 hours divided by 24, which is the slice of a day those 6.5 hours cover.

Example 3: Measure the Days Between Two Datetimes

Subtracting one datetime from another gives you a duration, which is the basis for any date difference in Power Query. You can pass that straight into Duration.TotalDays.

let
    StartTime = #datetime(2024,3,10,9,0,0),
    EndTime = #datetime(2024,3,15,21,0,0),
    Source = Duration.TotalDays(EndTime-StartTime)
in
    Source

Result: 5.5

The gap runs from March 10 at 9:00 AM to March 15 at 9:00 PM. That is 5 full days plus 12 extra hours, which is 5.5 days.

Example 4: Add a Days-Between Column to a Table

You have a table of projects with a start and end date for each one. You want a new column showing how many days each project ran. This is the same pattern you would use to add a reference column in Power Query.

Here is the starting data:

Project	Start	End
Alpha	2024-01-05	2024-01-20
Beta	2024-02-10	2024-03-01
Gamma	2024-03-15	2024-04-30

Subtract [Start] from [End] to get a duration, then run it through Duration.TotalDays.

let
    Source = Excel.CurrentWorkbook(){[Name="ProjectDates"]}[Content],
    AddDays = Table.AddColumn(Source, "DaysBetween", each Duration.TotalDays([End]-[Start]), type number)
in
    AddDays

The result produces:

Project	Start	End	DaysBetween
Alpha	2024-01-05	2024-01-20	15
Beta	2024-02-10	2024-03-01	20
Gamma	2024-03-15	2024-04-30	46

Each date difference becomes a duration of whole days, so the counts come out as clean integers. Alpha ran 15 days, Beta ran 20, and Gamma ran 46.

Things to keep in mind with Duration.TotalDays

Duration.TotalDays returns the whole span as a decimal. Duration.Days returns only the whole-day component as an integer. A #duration(0,6,30,0) value gives 0 from Duration.Days but about 0.27 from Duration.TotalDays.
A difference between two dates or datetimes is itself a duration. You can feed [End]-[Start] straight into the function with no conversion step.
A null input returns null, so rows with missing dates pass through without an error.
The result is a decimal, not a rounded value. If you only want whole days, wrap it in Number.Round.
Build duration literals with #duration(days,hours,minutes,seconds). So #duration(2,12,0,0) is 2 days and 12 hours.

Common questions about Duration.TotalDays

What is the difference between Duration.TotalDays and Duration.Days?

Duration.Days returns only the whole-day part of a duration as an integer. Duration.TotalDays returns the entire duration as a decimal, including the fraction contributed by hours, minutes, and seconds. For 6 hours 30 minutes, Duration.Days gives 0 while Duration.TotalDays gives about 0.27.

How do I get whole days only from Duration.TotalDays?

Wrap the result in Number.Round. For example, Number.Round(Duration.TotalDays([End]-[Start]),0) rounds the decimal day count to the nearest whole number.

List of All Power Query Functions

Related Power Query Functions / Articles: