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.

ðŸ“’ **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.

## 0 Comments