Learn how to subtract time in Excel with the help of this elaborate Microsoft Excel tutorial.

Working with time in Excel, particularly subtracting time from project start dates or employee timesheets, can sometimes be counterintuitive. Here’s why:

• Excel stores time as a decimal value: Unlike how we might think of time in hours and minutes, Excel treats it as a decimal number between 0 and 1. A full day equals 1, so one hour is equivalent to 1/24th of a day, which is 0.041666 in fractional value.
• Challenges with negative time values and exceeding 24 hours: Excel can struggle with negative time values (like when a task ends before it starts) and time values that go beyond a single day (more than 24 hours).

However, don’t worry! This guide will equip you with the knowledge to handle these time calculations effectively in Excel. You’ll be able to confidently work with time data in your spreadsheets by following along.

📒 Read More: 4 Ways to Add Time in Microsoft Excel

Using the Subtraction Operator

The easiest way to find the difference between two time values is to subtract the smaller value from the larger value using the minus sign. When the end time is always larger than the start time and within the same day or 24 hours, the calculation is fairly simple. However, it becomes complicated if the output is negative or the end time is the next day. Look at the exercises shown below:

Values Within 24 Hours

In the above dataset, the end time values are always larger than the start time values. Also, the end-time values are within 24 hours. Therefore, you can use a simple subtraction of the entire column C from column B to get elapsed or subtracted time values.

The formula you can use is as outlined below. Depending on the length of the dataset in both columns, you must adjust the cell range references.

=C2:C8-B2:B8

Press Enter to calculate the values for the entire column D.

The resulting values will be in fractions of 1, where 1 represents 24 hours.

So, you must select the entire column D until the last cell where data exists and press Ctrl + 1 to bring up the Format Cells dialog.

Now, click on the Custom number formatting category in the left-side navigation panel. On the right side, enter the following time formatting code in the Type field.

[h]:mm:ss

Click OK to save and enforce the time formatting code.

You should now see subtracted time values in the HH:MM:SS format in column D.

Values More Than 24 Hours

In the above Excel dataset, there are mixed values of start and end time where some entries of the End Time column go beyond 24 hours.

In this situation, you can use the following IF formula with the subtraction operator to always calculate the correct time difference in Excel:

=IF(C2<B2, C2+1-B2, C2-B2)

You must modify the formula according to your own input dataset.

Press the Enter key on the keyboard to calculate the value for the first cell.

Now, select the first cell and drag the fill handle down until values exist in the adjacent cell. This will copy the above formula to the rest of the cells in column D.

The fill handle will also calculate the cells as you drag it down.

Use the following custom formatting code to transform the decimal time values into the HH:MM:SS format:

[h]:mm:ss

Find above the final subtracted time values created by following this method.

Subtracting Time and Handling Negative Values

Microsoft Excel shows a never-ending series of # symbols when you subtract a large time value from a smaller one which results in a negative time.

If you ever need to get subtracted time values in the negative form, this is the ideal method to follow.

The sample dataset I’m using in this exercise is the one shown above.

This dataset shows the allotted time entries to complete the respective projects in column B and the actual time taken in column C.

So, if you use the following subtraction formula in D1, you’ll get a bunch of # symbols in the cells instead of a series of negative time values.

=B2:B8-C2:C8

Press Enter to calculate the entire column D.

You get the above result if column D has been set to the number formatting as Time.

To fix this, press Alt + F + T to bring up the Excel Options dialog box.

Click on the Advanced category in the left-side navigation panel.

On the right side, scroll down until you find When calculating this workbook section, and checkmark the checkbox for the Use 1904 date system option.

The # values will be converted to negative time entries as shown above. The calculation is still not accurate so far.

You must now select the used cell ranges in column D and apply the following custom formatting code using the Format Cells dialog box:

[h]:mm:ss

Click OK to apply the number formatting configuration.

You should now see time differences in negative values in the HH:MM:SS format.

Using the Text Function Command

This method enables you to learn using various Excel formulas using their dedicated command buttons on the Excel ribbon menu. In this exercise, I’ll show how to subtract time in Excel using the TEXT function so you can get a custom time formatting for the output value.

I’ve used the dataset shown above in this exercise.

Select the first cell in the column where you want to get the output of the subtracted time.

Click on the Text command button inside the Function Library block of the Formulas tab.

A context menu will open. From there, click on the TEXT function.

Excel will populate the TEXT() function syntax in the destination cell. It also shows a graphical user interface to guide you through entering the required values for the formula arguments.

Into the Value field, enter the cell range for Hours Worked Weekly, put the subtraction operator, and the cell range for Hours Weekly.

Enter "hh:mm" into the Format_text field.

Click OK to enter the final formula and calculate the cell range in column D.

Finally, you get subtracted time values in the text format.

Using the NOW Function

If you want to calculate the time elapsed for some ongoing projects, you can subtract the start time of those projects from the current time. For this, you can use the NOW function.

Find above the same dataset I’ve used in this exercise.

In C2, enter the following formula, and press the Enter key.

=NOW()-B2

Excel will calculate the selected cell and show a value of 1/3/1905 2:13 AM.

Select the cell and drag the fill handle down to populate the formula for the rest of the cells in column C.

The output values might look incorrect because they show a date entry 1/3/1905 whereas you wanted to get hours and minutes. To fix this, you just need to apply the following custom time formatting code using the Format Cells dialog box:

[h]:mm:ss

Finally, you get the subtracted time entries from the start of the projects until now in HH:MM:SS format as shown in the above screenshot.

Using Power Query

When working with massive datasets in Excel, subtracting start times from end times can become a slow process. Power Query offers a solution for this common task.

Importing a large database of time entries directly into Excel can bog things down. Power Query, a built-in data transformation tool, helps you avoid these performance issues.

It lets you clean up your raw data by removing unnecessary information before importing only the critical datasets you need into your Excel worksheet. This streamlined approach keeps Excel running smoothly, even with big data.

If you need to import an external database to Power Query, click on the Get Data drop-down menu in the Get & Transform Data block of the Data tab.

Depending on the source of the database, you need to hover the cursor on the appropriate menu, like From Database, From Azure, From Online Services, and so on.

If you choose From Database, you’ll find more options, like From SQL Server Database, From Oracle Database, etc.

Follow on-screen instructions to complete the data import to Power Query.

Contrarily, if you wish to export your worksheet’s dataset to Power Query, select the source dataset and click on the From Table/Range command button.

Click OK on the Create Table dialog box to complete the process.

You’ll see your dataset in Power Query. It’ll display the time entries in fractional values.

Go to the Add Column tab and click on the Custom Column option.

The Custom Column wizard will open. There, enter a name for the new column into the New column name field, like StartTime.

Into the Custom column formula field, enter the following Power Query M formula:

let
    DecimalTime = [Start Time],
    TotalMinutes = DecimalTime * 1440, // Convert days to minutes
    Hours = Number.IntegerDivide(TotalMinutes, 60),
    Minutes = Number.Mod(TotalMinutes, 60),
    TimeValue = Time.FromText(Text.PadStart(Text.From(Hours), 2, "0") & ":" & Text.PadStart(Text.From(Minutes), 2, "0"))
in
    TimeValue

Don’t forget to replace the Start Time column name in the above code according to the column name of your dataset.

Click OK to create the new column.

The new column, named StartTime, with transformed time values, will show up.

Now, repeat the same steps to create a new EndTime column with transformed time entries.

Now, delete previous time entry columns that are in decimal values.

Select the EndTime column first and then the StartTime column.

Now, go to the Add Column tab and click on the Time drop-down menu inside the From Date & Time block.

Click on the Subtract option in the context menu.

A new column will appear with the subtracted time entries.

Click on the File tab and choose the Close & Load To option.

Excel will take you to the active Excel worksheet. There, you’ll see the Import Data dialog box. Select the Existing worksheet option and select the destination cell on the worksheet.

Excel will import the transformed dataset from Power Query.

Delete redundant columns from the worksheet.

Now, select the Subtraction column and apply the [h]:mm:ss custom formatting to the column.

Congratulation! You’ve successfully subtracted time in Excel with Power Query.

Using Excel VBA

You can use Excel VBA to programmatically subtract time without setting up complex formulas. A VBA macro will show visual prompts so you can choose input data using the mouse. Once done feeding data, Excel will populate the output you require.

Before going ahead with the VBA script, go through the following Excel tutorial to learn how to create a VBA macro from a script:

📒 Read More: How To Use The VBA Code You Find Online

Find below the VBA script that’ll allow you to subtract time in an intuitive way:

Sub SubtractTimes()
    Dim endRange As Range
    Dim startRange As Range
    Dim destRange As Range
    Dim cellEnd As Range
    Dim cellStart As Range
    Dim cellDest As Range
    Dim endTime As Date
    Dim startTime As Date
    Dim timeDiff As Double

    ' Prompt user to select the range for end times
    On Error Resume Next
    Set endRange = Application.InputBox("Select the range for end times:", Type:=8)
    If endRange Is Nothing Then Exit Sub

    ' Prompt user to select the range for start times
    Set startRange = Application.InputBox("Select the range for start times:", Type:=8)
    If startRange Is Nothing Then Exit Sub

    ' Prompt user to select the range for destination cells
    Set destRange = Application.InputBox("Select the range for the destination cells:", Type:=8)
    If destRange Is Nothing Then Exit Sub

    ' Check if all ranges are of the same size
    If endRange.Rows.Count <> startRange.Rows.Count Or endRange.Columns.Count <> startRange.Columns.Count _
       Or endRange.Rows.Count <> destRange.Rows.Count Or endRange.Columns.Count <> destRange.Columns.Count Then
        MsgBox "The ranges must be of the same size."
        Exit Sub
    End If

    ' Iterate through each cell in the ranges
    For Each cellEnd In endRange
        Set cellStart = startRange.Cells(cellEnd.Row - endRange.Row + 1, cellEnd.Column - endRange.Column + 1)
        Set cellDest = destRange.Cells(cellEnd.Row - endRange.Row + 1, cellEnd.Column - endRange.Column + 1)

        ' Handle time differences
        endTime = cellEnd.Value
        startTime = cellStart.Value

        ' Calculate time difference
        timeDiff = endTime - startTime

        ' Adjust for negative differences (if the end time is on the next day)
        If timeDiff < 0 Then
            timeDiff = timeDiff + 1 ' Adds 24 hours
        End If

        ' Output result to destination cell
        cellDest.Value = timeDiff
        cellDest.NumberFormat = "[hh]:mm:ss" ' Format for time difference
    Next cellEnd

    MsgBox "Time subtraction complete!"

End Sub

After creating the VBA macro, press Alt + F8 to launch the Macro dialog box.

Select the SubtractTimes macro from the list and hit the Run button.

Once the script runs, it’ll show an input box so you can select the End Time column.

Another prompt will ask you to enter the cell range for the Start Time column.

A third input box will ask you to designate the destination cell range.

Finally, Excel will calculate and populate the subtracted time values in the designated column.

Conclusions

These are some of the proven methods to subtract time in Excel effectively, without making any errors in the calculation.

If you’re completely new to this skill, start with the manual methods, like the subtraction operator, the Text function command, and the NOW function.

You can use the Power Query-based method to get subtracted time values from a large dataset.

Finally, if you’re looking for a fully automated and programmatic method, practice the Excel VBA-based method.

If the article helped you, you can acknowledge it by commenting below. If you know a better method I missed, mention that in your comment.