How to Format Time Duration Values in Power BI Reports?

“How can I convert time duration values from decimal hours into hours and minutes format in Power BI? Our project tracking data currently shows durations as plain decimal numbers, such as 1.5 or 2.25, but we need to display them in a format that is easier for stakeholders to understand and interpret quickly.”

Understanding Time Duration Formatting Challenges

When time is expressed in raw decimal values like 1.5 or 2.25 hours, it can be difficult for non-technical stakeholders to intuitively grasp the duration represented. These values require mental conversion, which can slow down decision-making and reduce clarity. Power BI provides several effective techniques to transform these decimal hour values into more human-readable formats, such as “1:30” for one and a half hours or “2:15” for two hours and fifteen minutes. In this tutorial, we’ll demonstrate how to perform these conversions using both Power Query (M language) and DAX (Data Analysis Expressions) in Power BI Desktop 2023, ensuring flexibility depending on where in the data pipeline you prefer to handle formatting.

Project Time Tracking Data Model

For the purpose of this tutorial, we’ll be working with a sample table named "ProjectTasks" that contains the following columns relevant for tracking time:

TaskID (Text) – A unique identifier for each task
TaskName (Text) – The name or title of the task
PlannedDuration (Decimal) – The expected duration of the task in decimal hours
ActualDuration (Decimal) – The real, logged duration of the task in decimal hours
StartDate (DateTime) – The timestamp when the task began
CompletionDate (DateTime) – The timestamp when the task was completed

This structure provides the foundation for measuring planned vs. actual effort and applying formatting logic accordingly.

Converting Duration Values Step-by-Step

1. Open Power Query Editor and Add a Formatted Duration Column

let
    Source = ProjectTasks,
    ConvertedDuration = Table.AddColumn(
        Source, 
        "FormattedDuration",
        each Time.FromText(
            Text.From(Int([ActualDuration])) & ":" & 
            Text.From(Number.Round(([ActualDuration] - Int([ActualDuration])) * 60))
        )
    )
in
    ConvertedDuration

2. Create a DAX Measure for Dynamic Duration Formatting

Formatted Duration = 
VAR Hours = INT(ProjectTasks[ActualDuration])
VAR Minutes = INT((ProjectTasks[ActualDuration] - Hours) * 60)
RETURN Hours & ":" & FORMAT(Minutes, "00")

3. Add a Calculated Column for Static Display

Duration Display = 
FORMAT(INT([ActualDuration]), "00") & ":" & 
FORMAT(INT(([ActualDuration] - INT([ActualDuration])) * 60), "00")

4. For Advanced Logic, Create a Duration Format Function in Power Query

(DurationValue as number) as text =>
let
    Hours = Number.IntegerDivide(DurationValue, 1),
    Minutes = Number.Round((DurationValue - Hours) * 60),
    FormattedTime = Text.From(Hours) & ":" & 
                   Text.PadStart(Text.From(Minutes), 2, "0")
in
    FormattedTime

5. Apply Conditional Formatting to Visualize Duration Insights

Select your duration column or DAX measure in a visual (e.g., table or matrix)
Navigate to Column Tools > Conditional Formatting
Define custom formatting rules, for instance, coloring tasks exceeding 3 hours in red

Pro Tip: If your reports are being consumed internationally, use Power BI’s locale settings to align date and time display formats with regional preferences, avoiding confusion in time interpretation.

Keep duration math separate from duration display

Before changing the configuration, establish one known-good test and preserve anything that may be difficult to recover. Create a separate formatted value for labels, tooltips, or tables. Write down the account, client or device, and exact symptom so that later tests use the same conditions. Change one variable at a time; several simultaneous fixes can hide the real cause and make the problem return.

Check the deciding condition

Test totals above 24 hours and blank or negative values. Repeat the test with a simple sample that contains no confidential or business-critical data. If the sample works, compare permissions, file location, policy, and content with the failing case instead of reinstalling the whole product.

Document whether the source unit is seconds, minutes, hours, or days. Keep the original configuration or data until the new result has been checked. When a setting is unavailable, grayed out, or labeled as organization-managed, capture that message and ask the responsible administrator to review the policy.

Test the outcome without widening the change

undefined. Confirm the result after closing and reopening the relevant app, then repeat it from the normal user workflow rather than only from an administrator or owner account. A successful one-time test is not enough when synchronization, scheduled refresh, or another device is involved.

If behavior differs across clients, record the app version, account type, time of the test, and where the expected result appears. Use that evidence to decide whether the remaining fault is local, account-specific, data-specific, or service-side. This keeps troubleshooting narrow and protects working settings while support investigates the correct layer.

Finally, explain the intended outcome to another user and have that person repeat the shortest safe test. Their result can reveal hidden owner privileges, cached state, or assumptions about where data is stored. Preserve screenshots of error messages only after removing names, addresses, document content, and other private information.

Common Time Duration Formatting Issues

Missing or Invalid Duration Values

Error: "Expression.Error: Invalid duration value"
Solution: Add error handling in Power Query to replace null or malformed values with a default of 0 using try...otherwise
In DAX, use IFERROR() or ISBLANK() checks to gracefully manage edge cases

Decimal Precision Problems

Issue: Minor rounding discrepancies in minute conversion (e.g., showing “1:59” instead of “2:00”)
Fix: Use ROUNDUP() or ROUNDDOWN() depending on your desired accuracy, and consider formatting values consistently to one or two decimal places before transformation

Format Consistency

Challenge: Source data may contain a mix of integer, decimal, or even string values
Resolution: Normalize all duration values to decimal hour format first before applying formatting
Introduce a validation or cleaning step in Power Query to enforce data type consistency across records