Duration.Hours returns the hours component of a duration value as a whole number. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
If you have a duration and want just the hours slot out of it, this is the function you reach for. It pulls the hours part on its own, separate from the days, minutes, and seconds.
Syntax of Duration.Hours Function
Duration.Hours(duration as nullable duration) as nullable number
where
duration(required, nullable duration). The duration value to read the hours portion from. This is usually a#duration(days,hours,minutes,seconds)literal, or the result of subtracting two#datetime,#date, or#timevalues.
Returns: a number, the hours component of the duration as a whole number from 0 to 23. If duration is null, it returns null.
In plain terms, it gives you the hours slot of the duration, not the total number of hours. A duration of 1 day and 5 hours returns 5, not 29.
Example 1: Get the hours component of a duration
Read the hours part out of a duration of 1 day, 5 hours, and 30 minutes.
Duration.Hours(#duration(1,5,30,0))
Result: 5
The duration carries 5 in its hours slot, so the function returns 5. The 1 day and the 30 minutes are ignored because they sit in other slots.
Example 2: Component hours vs total hours
This is the part that trips people up. Duration.Hours returns only the hours slot, while Duration.TotalHours rolls the whole duration up into hours.
Start with Duration.Hours on the same duration as before:
Duration.Hours(#duration(1,5,30,0))
Result: 5
Now run Duration.TotalHours on the exact same duration:
Duration.TotalHours(#duration(1,5,30,0))
Result: 29.5
Same duration, two very different numbers. Duration.Hours gives you the hours slot (5), while Duration.TotalHours converts everything to hours: 24 for the day, plus 5 hours, plus 0.5 for the 30 minutes, which is 29.5.
If you want the actual length of time in hours, you want Duration.TotalHours.
Example 3: Hours component from subtracting two datetimes
You can build a duration by subtracting one #datetime from another, then read its hours component.
let
StartTime = #datetime(2024,1,1,8,0,0),
EndTime = #datetime(2024,1,3,17,45,0),
Elapsed = EndTime - StartTime,
Source = Duration.Hours(Elapsed)
in
Source
Result: 9
The gap between the two times is 2 days, 9 hours, and 45 minutes. Duration.Hours returns the hours slot, which is 9. The 2 days and 45 minutes live in their own slots.
Example 4: Add an hours-component column to a table
Most of the time you will be doing this across a whole table, one hours component per row.
Say you have a ShiftLog query that records the start and end of each shift as separate year, month, day, and hour columns.
Here is the starting data:
| StartYear | StartMonth | StartDay | StartHour | EndYear | EndMonth | EndDay | EndHour |
|---|---|---|---|---|---|---|---|
| 2024 | 3 | 1 | 9 | 2024 | 3 | 2 | 17 |
| 2024 | 3 | 5 | 14 | 2024 | 3 | 6 | 1 |
| 2024 | 3 | 10 | 6 | 2024 | 3 | 12 | 9 |
| 2024 | 3 | 15 | 23 | 2024 | 3 | 16 | 5 |
Now build the end and start #datetime values, subtract them, and read the hours component into a new column:
let
Source = Excel.CurrentWorkbook(){[Name="ShiftLog"]}[Content],
AddHoursComponent = Table.AddColumn(Source,"HoursComponent",each Duration.Hours(#datetime([EndYear],[EndMonth],[EndDay],[EndHour],0,0) - #datetime([StartYear],[StartMonth],[StartDay],[StartHour],0,0)),Int64.Type)
in
AddHoursComponent
The new HoursComponent column holds the hours slot of each shift’s length:
| StartYear | StartMonth | StartDay | StartHour | EndYear | EndMonth | EndDay | EndHour | HoursComponent |
|---|---|---|---|---|---|---|---|---|
| 2024 | 3 | 1 | 9 | 2024 | 3 | 2 | 17 | 8 |
| 2024 | 3 | 5 | 14 | 2024 | 3 | 6 | 1 | 11 |
| 2024 | 3 | 10 | 6 | 2024 | 3 | 12 | 9 | 3 |
| 2024 | 3 | 15 | 23 | 2024 | 3 | 16 | 5 | 6 |
Note the first row: the shift runs 1 day and 8 hours, so HoursComponent is 8, not 32. The component never counts the whole-day part.
Things to keep in mind with Duration.Hours
- It returns the hours component, not the total hours. This is the number-one source of confusion. A duration of 1 day, 5 hours returns
5, not 29. For elapsed-time math where you want the full length in hours, useDuration.TotalHoursinstead. - The result never goes above 23. Anything 24 hours or more rolls into the days slot, so
#duration(0,36,0,0)normalizes to 1 day 12 hours andDuration.Hoursreturns12, not 36. - It is one of a family of four.
Duration.Days,Duration.Hours,Duration.Minutes, andDuration.Secondseach pull a single slot, and together they reconstruct the whole duration. nullin givesnullout.Duration.Hours(null)returnsnullrather than erroring, which matters when a column has blanks.
Common questions about Duration.Hours
What is the difference between Duration.Hours and Duration.TotalHours?
Duration.Hours returns the hours slot only, a whole number from 0 to 23. Duration.TotalHours converts the entire duration into hours and returns a fractional number with no cap. For #duration(1,5,30,0) you get 5 versus 29.5 (see Example 2).
How do I get the total hours between two times or dates?
Subtract the two #datetime values to get a duration, then wrap it in Duration.TotalHours, not Duration.Hours. The total hours version includes the whole-day part and the fractional minutes, which is what you usually want for elapsed time. The same subtraction idea is covered for whole dates in this guide on how to calculate date difference in Power Query.
List of All Power Query Functions
Related Power Query Functions / Articles:
