# How to Subtract Times in Excel like a Pro

Use the TIME function in Excel to add or subtract hours, minutes and seconds. To add up times in Excel, simply use the SUM function.

Calculating time differences in Excel can be tricky, but it’s an important skill for tracking hours worked, scheduling projects, and more. Whether you’re a complete beginner or an Excel veteran, this comprehensive guide will teach you multiple methods for subtracting times in Excel. From basic time functions to advanced formatting techniques, we’ve got you covered. Let’s dive in!

## Why Subtract Times in Excel?

Before jumping into the step-by-step instructions, let’s discuss some common reasons you might need to subtract times in Excel:

• Timesheets – Calculate total hours worked by subtracting start and end times.
• Scheduling – Find the duration of tasks or events by subtracting start and end times.
• Billing – Determine billable hours by subtracting time in and time out.
• Operations – Measure process cycle times by subtracting start and stop times.
• Logistics – Calculate transit, waiting, or service times by subtracting timestamps.

As you can see, time subtraction has many practical uses in business, project management, and everyday life Mastering this skill will make you more productive in Excel.

## Prerequisites – Format Cells as Times

Before subtracting times in Excel, you need to format the cells containing the times as time values instead of plain numbers.

Here are two quick ways to format cells as times in Excel

• Select the cells, click the Number Format drop-down on the Home tab, and pick Time

• Or, right-click the cells, select Format Cells, go to the Number tab, and choose Time from the list.

Formatting as time ensures accurate math when subtracting start and end times.

## Method 1 – Time Functions

The simplest way to find time differences in Excel is using the HOUR, MINUTE, and SECOND functions.

Here’s the basic syntax:“`
=HOUR(end_time – start_time)

=MINUTE(end_time – start_time)

=SECOND(end_time – start_time) “`

This returns the total number of hours, minutes, or seconds between two time values.

For example, to get the hours between the times in cells B2 and B1:` =HOUR(B2-B1)`

To get just the minutes:` =MINUTE(B2-B1)`

And to get just the seconds:` =SECOND(B2-B1)`

The major limitation is that you can only display the hours, minutes, or seconds – not combinations like hours and minutes together. For that, we’ll need a different approach.

## Method 2 – Subtract Times and Format

To display the time difference with hours and minutes (or with seconds also), you can simply subtract the times and then format the result as desired.

1. In a cell, subtract your start and end times using the minus sign. For example: ` =B2-B1 `

2. Select the cell with the formula result.

3. On the Home tab, open the Number Format drop-down and select More Number Formats.

4. In the Format Cells dialog, select Custom from the Category list.

5. In the Type field, choose the desired format: – h:mm for hours and minutes
– h:mm:ss for hours, minutes, and seconds

This will nicely display the full time difference in hours, minutes, and optional seconds!

The only extra step is selecting the custom format. But it gives you full control over the display.

## Method 3 – TEXT Function

Lastly, you can use Excel’s TEXT function to subtract times and format the result in one step.

The syntax is:` =TEXT(end_time - start_time, "format")`

Where “format” is your desired formatting surrounded by quotes.

For example:` =TEXT(B2-B1,"h:mm")`

Displays the time difference in hours and minutes.

To also show seconds:`=TEXT(B2-B1,"h:mm:ss")`

The TEXT approach requires less steps since the formatting is built-in. But, the result is text formatted instead of true Excel time values.

• Time functions give basic hours, minutes, or seconds
• Subtract and format provides flexible combinations
• TEXT function does it all in one formula

Use the method that best fits your particular needs.

## Tips for Accurate Time Math

To avoid errors when subtracting times in Excel, follow these best practices:

• Format cells as times before subtracting.
• Use consistent time formats like h:mm:ss AM/PM.
• Subtract smaller time from larger time to get positive duration.
• Don’t subtract milliseconds – truncate to seconds.
• Test formulas on sample data first.
• Double check custom format codes for proper order.
• Review formatting of formula cells and confirm correct display.

Taking a minute to follow these tips will ensure you get precise, reliable time math in Excel.

## Common Uses for Time Subtraction

Now that you’re a master at subtracting times in Excel, let’s discuss some of the most common real-world uses for this important skill:

• Timesheets – Enter start and end times in Excel to calculate hours worked.
• Scheduling – Build a schedule template that subtracts times to get event durations.
• Billing – Invoice clients based on billable hours obtained by subtracting times.
• Payroll – Integrate punch in/out times with your payroll system to pay employees.
• Production – Analyze process cycle times by subtracting timestamps.
• Logistics – Optimize routes and resources by studying service times.
• Quality Control – Identify bottlenecks based on time gaps between process steps.

The applications are endless! Applying your Excel time subtraction skills will make you more effective and efficient across all facets of business and project management.

## Next Level: Conditional Time Formatting

When you build timesheets, schedules, or dashboards using time math, consider adding conditional formatting to visually highlight time thresholds.

For example, format cells:

• Green if less than 8 hours
• Yellow between 8-10 hours
• Red if greater than 10 hours

This allows you to see at a glance where durations exceed targets or alert levels.

Use Excel’s built-in Conditional Formatting tools to automatically visualize your time data.

## Get Subtracting!

Understanding how to subtract two times is an important Excel skill for tracking hours, scheduling, project management, and more.

In this comprehensive guide, you learned:

• Time functions to get basic time units like hours
• Subtracting times and formatting for flexible combinations
• The TEXT function to do everything in one step
• Best practices for accurate time math
• Real-world applications to make you more productive
• Creative conditional formatting to visualize time data

Now put your skills into practice! Subscribe to our blog for more awesome guides, tips, and tutorials to take your Excel mastery to new heights.

What time subtraction challenges have you faced in Excel? Share your experiences in the comments below!

### Add Hours, Minutes and Seconds

Remember, to enter a time, use the “:” (colon).

1. Enter a time into cell A1.

2. To add 2 hours and 30 minutes to this time, enter the formula shown below.

Explanation: the TIME function has 3 arguments. First, use HOUR, MINUTE and SECOND to create the time entered in cell A1. Next, add 2 hours and 30 minutes.

To change the Time format, execute the following steps.

3. Select cell B1.

4. Right click, and then click Format Cells (or press CTRL + 1).

5. In the Category list, select Time, and select a Time format.

7. Enter a time into cell A1 (use 2 colons to include seconds).

8. To add 10 seconds to this time, enter the formula shown below.

Note: we changed the Time format to 13:30:55 (see step 5).

To add up times in Excel, simply use the SUM function. To display a total of more than 24 hours, change the Time format.

1. For example, task A requires 8 hours to complete, task B requires 4 hours and 30 minutes to complete, etc.

2. To calculate the total duration, simply use the SUM function.

3. Change the duration of task D from 5 hours to 7 hours.

To display a total of more than 24 hours, execute the following steps.

4. Select cell B6.

5. Right click, and then click Format Cells (or press CTRL + 1).

6. Select the Custom category.

7. Type the following number format code: [h]:mm

To subtract hours, minutes or seconds from a time, use the TIME function (and HOUR, MINUTE and SECOND).

1. Enter a time into cell A1.

2. To subtract 33 minutes from this time, enter the formula shown below.

Calculating the difference between two times in Excel can be tricky.

1. To calculate the difference between two times in the same day, simply subtract the start time from the end time.

Note: we changed the Time format to 13:30 (see step 5 under “Add Hours, Minutes and Seconds”).

2. This simple formula doesnt work if the start time is before midnight and the end time is after midnight. The Time Difference formula below always works.

3. The formula below calculates the difference between two times in hours as a decimal value.

• ### Next Chapter

• Text Functions

## FAQ

What is the formula for subtracting time in Excel?

 Formula Explanation =A2-B2 Calculates the difference between the time values in cells A2 and B2. =TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”) Calculates the difference between the specified times.

How do I subtract hours from a time in Excel?

To subtract hours, minutes or seconds from a time, use the TIME function (and HOUR, MINUTE and SECOND). 1. Enter a time into cell A1. 2. To subtract 33 minutes from this time, enter the formula shown below. Calculating the difference between two times in Excel can be tricky.

How to calculate time between two cells in Excel?

Use the formula ‘= (C2-A2)’ to solve the problem. This formula calculates the amount of time that has passed between these two cells and presents it in hours. You can make this calculation much more complicated by including dates. If you work hours that last more than 24hrs or involve two days in a shift, this method will come in handy.

How to calculate minutes in Excel?

The MINUTE Function Returns the minute as a number (0-59). To use … excel calculate minutes between times Details: Excel can calculate how many hours and minutes (the amount of time) it will take to complete two tasks. It can also take the start and end times for a task and calculate the elapsed time, which is the difference between the two times.