# 6 Ways To Subtract Time in Microsoft Excel

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/24`th 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.

## 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.

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),
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.

### Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

### Subscribe

Subscribe for awesome Microsoft Excel videos ðŸ˜ƒ

## Related Posts

#### 7 Ways To Reference Another Sheet in Microsoft Excel

This Microsoft Excel tutorial will show you how to reference another sheet in...

#### 10 Ways To Rank in Microsoft Excel

If you wish to learn the must-have skill of how to rank in Excel, follow along...

#### 8 Ways To Store Numbers as Text in Microsoft Excel

This easy and elaborate Microsoft Excel tutorial will help you learn how to...