If you want to pull the number of whole days out of a duration in Power Query, or work out how many days sit between two dates, the Duration.Days function is what you reach for.
It takes a duration value and hands back just the day part of it as a plain number.
Syntax of Duration.Days Function
Duration.Days(duration as nullable duration) as nullable number
where
duration(required, nullable duration). The duration value whose whole-days component you want. A duration often comes from subtracting one date or datetime from another.
Returns: the whole-days component of the duration as a number, that is the day part only and not the total span measured in days. If duration is null, it returns null.
In plain terms, Duration.Days(#duration(5,12,0,0)) returns 5, not 5.5. Those extra 12 hours sit in their own bucket. If you want the full fractional total instead, use Duration.TotalDays.
Example 1: Get the day component of a duration
Pull the whole-days part out of a duration of 5 days and 12 hours.
Duration.Days(#duration(5,12,0,0))
Result: 5
The #duration(days,hours,minutes,seconds) literal builds a value of 5 days and 12 hours, and Duration.Days hands back just the day part.
Example 2: Get the whole days between two dates
Subtracting one date from another gives you a duration, so you can wrap that directly.
Duration.Days(#date(2024,7,22) - #date(2024,7,8))
Result: 14
#date(2024,7,22) - #date(2024,7,8) produces a 14-day duration, and Duration.Days reports 14.
Example 3: The day component ignores leftover hours
This is the part that trips people up. Feed it 3 days and 20 hours.
Duration.Days(#duration(3,20,0,0))
Result: 3
Duration.Days only reports complete days, so the 20 leftover hours are dropped and you get 3. If you wanted the full span here, Duration.TotalDays would return about 3.83 instead.
Example 4: Count the days across a leap year
The same date-subtraction trick works across a full year.
Duration.Days(#date(2025,1,1) - #date(2024,1,1))
Result: 366
2024 is a leap year, so the span from January 1, 2024 to January 1, 2025 is 366 days rather than 365.
Things to keep in mind with Duration.Days
- The input has to be a duration. Passing a number or a date throws an error. Convert with
#duration(...)or by subtracting dates first. - A negative duration gives a negative day count. If you subtract a later date from an earlier one, the duration is negative and
Duration.Daysreturns a negative number. Subtract in the right order, or wrap the result inNumber.Abs. - It is plain scalar arithmetic. There is nothing to fold back to a source, so it runs cheap whether you point it at one value or a whole column.
Common questions about Duration.Days
What’s the difference between Duration.Days and Duration.TotalDays?
Duration.Days gives you only the whole-day component, so 3 days and 20 hours comes back as 3. Duration.TotalDays gives the entire span as a decimal, so the same input comes back as about 3.83. Use Duration.Days when you want complete days, and Duration.TotalDays when the leftover hours matter.
How do I get the number of days between two dates?
Subtract the earlier date from the later one to get a duration, then wrap it in Duration.Days for whole days, like Duration.Days([EndDate] - [StartDate]). For a fuller walkthrough see calculate date difference in Power Query. If you need a fractional day count for datetimes, use Duration.TotalDays instead.
List of All Power Query Functions
Related Power Query Functions / Articles:
