How to Subtract Two Time Columns in Power BI

Figuring out the time difference between two events is a fundamental task in data analysis, but subtracting two time columns in Power BI can be surprisingly tricky. Unlike simple numbers, time calculations come with eccentricities around data types and formatting. This tutorial will walk you through several straightforward methods to calculate the duration between a start and end time, whether you prefer using DAX or Power Query.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Simple Subtraction Can Be Misleading

You might think you can just create a new column with a formula like [End Time] - [Start Time]. While this is the right first step, the result might not look like what you expect. Power BI performs the calculation correctly, but it returns the result as a Duration data type, which is internally stored as a decimal number representing the fraction of a 24-hour day.

For example, a difference of 6 hours will show up as 0.25 (since 6 hours is one-quarter of a 24-hour day). While factually correct, this isn't very readable for a report. The key is knowing how to perform the subtraction and then format the result into a format humans can easily understand, like hours, minutes, or a standard hh:mm:ss string.

Method 1: Direct Subtraction and Formatting in DAX

This is the quickest method for getting a visual representation of the duration directly within your tables and visuals. It involves creating a calculated column and then changing its format.

Let's assume you have a table named 'TaskTimes' with two columns: [StartTime] and [EndTime], both set to the Time data type.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Instructions:

1. From the Report View or Data View in Power BI, select your table.
2. In the ribbon, go to the Table Tools tab and click on New Column.
3. Enter the following DAX formula into the formula bar:
4. Press Enter. You will now see a new column named Duration filled with decimal numbers.
5. Select the new Duration column in the Data pane on the right.
6. In the ribbon, the Column Tools tab will appear. Find the Formatting section.
7. Click the Format drop-down menu and choose Time, then select a format like hh:mm:ss from the menu below it. (Note: in some versions of Power BI, you may need to find 'Custom Format' and type hh:mm:ss yourself).

Your column will now display the duration in a familiar time format. This method is perfect for display purposes, but if you need to perform further calculations like summing or averaging these durations, it's better to convert them to a standard number first, as shown in the next method.

Method 2: Using DATEDIFF to Get Total Seconds, Minutes, or Hours

When you need the duration as a single numeric value for aggregation or other calculations, the DATEDIFF function in DAX is your best friend. This function calculates the number of interval boundaries crossed between two dates or times.

This is extremely useful if you want to answer questions like, "What was the average task completion time in hours?" or "What's the total time spent on all tasks in minutes?"

Here are the formulas to create new calculated columns for each unit.

Duration in Total Hours (with Decimals)

Using DATEDIFF with the HOUR interval will round down to the nearest whole hour, which is often not precise enough. A more accurate approach is to calculate the difference in minutes and divide by 60.

Duration in Hours = 
DATEDIFF(
    'TaskTimes'[StartTime], 
    'TaskTimes'[EndTime], 
    MINUTE
) / 60

Duration in Total Minutes

Duration in Minutes = 
DATEDIFF(
    'TaskTimes'[StartTime], 
    'TaskTimes'[EndTime], 
    MINUTE
)

Duration in Total Seconds

Duration in Seconds = 
DATEDIFF(
    'TaskTimes'[StartTime], 
    'TaskTimes'[EndTime], 
    SECOND
)

After creating these columns, make sure to set their data type to Decimal Number or Whole Number in the Column Tools tab. You can now use these numeric columns in calculations like SUM, AVERAGE, MIN, or MAX to get meaningful insights from your time data.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: Creating a Custom Formatted hh:mm:ss Text String

Sometimes you need complete control over how the duration is displayed, especially if you want to include it within a text-based visual or concatenate it with other strings. In this case, you can build a formatted text string that looks like hh:mm:ss.

This multi-step approach is more robust because it isn't dependent on Power BI's built-in formatting options. The basic idea is to get the total seconds and then use simple math to calculate the hours, minutes, and remaining seconds.

Step-by-Step DAX Formula:

You can combine these steps into a single, clean formula using DAX variables.

1. Create a new calculated column.
2. Enter the complete formula below:

Duration Formatted = 
VAR TotalSeconds = DATEDIFF('TaskTimes'[StartTime], 'TaskTimes'[EndTime], SECOND)
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN
    FORMAT(Hours, "00") & ":" & 
    FORMAT(Minutes, "00") & ":" & 
    FORMAT(Seconds, "00")

How This Formula Works:

• TotalSeconds: A variable that stores the total duration in seconds using DATEDIFF.
• Hours: Calculates the number of whole hours by dividing the total seconds by 3600 (the number of seconds in an hour) and discarding any remainder with INT.
• Minutes: First, MOD(TotalSeconds, 3600) gets the remaining seconds after the hours are accounted for. Then, dividing that by 60 gives the minutes.
• Seconds: MOD(TotalSeconds, 60) calculates the seconds remaining after full minutes.
• RETURN: The FORMAT function ensures each component has a leading zero if it's a single digit, and & operators concatenate everything into the final hh:mm:ss string.

Explanation:

• TotalSeconds computes total duration in seconds.
• Hours, Minutes, and Seconds extract respective parts.
• The final string is formatted with leading zeros for consistency and concatenated into a standard time format.

Method 4: Subtracting Time in Power Query

If you prefer to clean and prepare your data before it even loads into the Power BI data model, you can perform these subtractions in the Power Query Editor. This is often more efficient for very large datasets.

Step-by-Step Instructions:

1. In Power BI Desktop, click on Transform data in the home ribbon to open the Power Query Editor.
2. Select the query containing your time columns.
3. Make sure both the [StartTime] and [EndTime] columns are set to the Time data type. You can check or change this by clicking the icon in the column header.
4. Select the [EndTime] column. Now, press and hold the Ctrl key and select the [StartTime] column. The order is important: 'End' first, then 'Start'.
5. Go to the Add Column tab in the ribbon.
6. Click on the Time button and choose Subtract from the dropdown menu.
7. A new column named 'Subtraction' will be created, holding the duration value.

From here, you can easily transform this new duration column into total hours, minutes, or seconds.

• Select the Subtraction (duration) column.
• On the Add Column tab, click the Duration button.
• From the dropdown, you can select Total Hours, Total Minutes, or Total Seconds to create a new column with the duration as a decimal number.

This no-code approach in Power Query allows you to handle all your time calculations cleanly during the data ingestion process.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Common Problem: Calculating Durations That Cross Midnight

A frequent headache arises when dealing with activities that span across midnight, like night shifts (e.g., Start: 10:00 PM, End: 6:00 AM). A simple subtraction like 06:00 - 22:00 will produce a negative result because the end time is "smaller" than the start time.

You can fix this with a simple IF statement in DAX. The logic is: if the end time is less than the start time, we know it crossed midnight, so we add 1 (representing a full 24-hour day) to the end time before subtracting.

Duration Across Midnight = 
IF(
    'TaskTimes'[EndTime] < 'TaskTimes'[StartTime],
    (1 + 'TaskTimes'[EndTime]) - 'TaskTimes'[StartTime],
    'TaskTimes'[EndTime] - 'TaskTimes'[StartTime]
)

Once you have this new calculated column, you can format it as hh:mm:ss just like in Method 1.

Final Thoughts

Whether you use DAX or Power Query, subtracting one time column from another in Power BI is all about choosing the right method for your goal. You can perform a direct subtraction for quick display purposes, use DATEDIFF to get numeric values for aggregation, build a custom text string for full control, or handle it all in Power Query before the data is even loaded.

This process of writing formulas, handling edge cases, and wrangling data models can often feel like time-consuming busy work. At Graphed, we are building a solution where you can connect your data sources and get answers without the steep learning curve. Instead of DAX, you can simply ask, "What was the average time difference between the start and end columns for each project last month?", and our AI analyst will build an interactive dashboard for you in seconds, saving you from the complexities of time calculations altogether.