How to subtract times in Excel (with tips and formulas)

By Indeed Editorial Team

Published 25 May 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Microsoft Excel is a spreadsheet application that allows you to use formulas to identify specific relationships between values found within cells of your spreadsheet. These formulas are tools that allow you to perform addition and subtraction, find percentages, divide and even determine averages. Unlocking this potential requires an understanding of the formulas used in Excel. In this article, we focus on how to subtract times in Excel and other ways to find time values using formulas in this spreadsheet application.

How to subtract times in Excel to find time difference

Learning how to subtract times in Excel requires an understanding of the formulas used in this application to interact with values found in cells within your spreadsheet. There's more than one way to do this and the best way depends on the data you have and the result you're looking for. Below are the different ways that you can calculate time differences in Excel by subtracting times:

1. Subtracting one time from another time

Excel doesn't format times in a clearly understood way. Instead, it uses decimals to format times. Although this makes it difficult to understand at first glance, it does make it easier to add and subtract times as if they were a standard number. The most basic formula used to subtract times in Excel is:

=End time - start time

The output of this formula changes depending on the data set available to you, so it could look like any of the following outputs:

  • =A2-B2: This output calculates the difference between the time values found in cells A2 and B2.

  • =TIMEVALUE ("9:30PM") - TIMEVALUE ("1.00PM"): This uses specific timeframes to calculate differences in times.

  • =TIME(HOUR (A1), MINUTE (A1), SECOND (A1)) - TIME (HOUR(B1), MINUTE (B1), SECOND (B1)): This calculates the difference in time between values in cells A1 and B1 but ignores the difference in dates if the cells include both date and time.

Transforming decimal time into real time

The system that Excel uses to represent time focuses on fractions of decimal numbers, so your output is a format like 0.15225245. This is useful from a formulaic point of view, but it doesn't offer much meaning in terms of time. You can implement a custom format to transform the decimal time into a real-time one that's more legible. Below is a list of these custom time formats:

  • h: Number of hours elapsed.

  • h:mm: Number of hours and minutes elapsed.

  • h:mm:ss: Number of hours, minutes and seconds elapsed.

You can apply these custom formats for time by clicking Ctrl + 1 to pull up the 'format cells' dialog box. From here, click 'custom' from 'category list' and enter the time codes you'd like in the 'type box'. Also, if the elapsed time appears as hash symbols (##) then the cell is either too short to display the time correctly or the calculation has created a negative value.

Related: What is a dialog box? A beginner guide in programming

  1. Using the TEXT function to subtract times in Excel

Another approach you can use to subtract two times in Excel is to implement the TEXT function. This helps in a few different ways depending on your desired output, as seen below:

  • =TEXT (B1-A1, "h"): Calculate the hours between two times.

  • =TEXT (B1-A1, h:mm): Find the hours and minutes between two times.

  • =TEXT (B1-A1, "h:mm:ss"): Find the hours, minutes and seconds between two times.

It's worth noting that if the end result is a negative number, then the TEXT formula gives you a #VALUE! error.

3. Calculate hours, minutes or seconds between two timeframes

You can find the time difference in hours, minutes or seconds in Excel with a few different calculations such as:

Calculating hours between two timeframes

To view results that are decimal, use the following formula:

=(End time - start time) x 24

If the starting time is in cell A1 and the end time is in B1, the equation B1-A1 calculates the difference between these two times. From here, multiply by 24 to denote the number of hours in a given day. If you want to determine the number of complete hours, you can implement the INT function which rounds the final result to the nearest integer. This formula is:

=INT ((B1-A1) x 24)

Calculating total minutes between two timeframes

If you intend to calculate the minutes between two different times, multiply the difference in time by 1440 to denote the number of minutes in a day. The formula for this is:

=(End time - start time) x 1440

If the end time is less than the start time, then the formula presents a negative value.

Calculating total seconds between two timeframes

You can also find the total amount of seconds between two timeframes in a similar way. The only difference is multiplying by the total amount of seconds in a day instead of minutes, which is 86,400 seconds. The formula for this is:

=(End time - start time) x 86400 Related: How to make a flowchart in Excel including flowchart uses

4. Calculate the time difference in a single time unit

You can also find the difference between two times in a specific unit of time without including other increments. This works with one of the following formulas:

  • =HOUR (B1-A1): Difference in hours (without minutes or seconds).

  • =MINUTE (B1-A1): Difference in minutes (without hours or seconds).

  • =SECOND (B1-A1): Difference in seconds (without hours or minutes).

If you're using the HOUR, MINUTE or SECOND functions in Excel, it's worth remembering that the result cannot extend beyond 24 for hours or 60 for minutes and seconds. If the end time is less than the start time, you might also receive a #NUM! error.

Related: How to create a budget in Excel (plus budgeting tips)

5. Calculate the elapsed time from a starting point to now

You can determine how much time has elapsed from a set starting time until now in Excel. To do this, you can use the NOW function to find the current time and then subtract the defined start date from it. Using cell A1 as our start time, we can use the following formula:

=NOW()-A1

If the elapsed time goes beyond 24 hours, then you might use an appropriate time format such as 'd' for days. If the start time doesn't contain any dates, then you can use the TIME function to find the correct elapsed time. It's also worth noting that the elapsed time isn't updated in real-time and only updates when opening Excel. You can force an update by pressing shift + F9 or simply pressing F9 if you want to recalculate all open spreadsheets in Excel. Related: How to use data consolidation in Excel: a complete guide

6. Showing time differences in days, hours, minutes and seconds

In terms of ease of use, this is probably the easiest formula to use in Excel for determining time. It works by implementing the HOUR, MINUTE and SECOND functions in a way that presents time units with the INT function determining the difference in days. This can work as a single formula:

=INT (B1-A1) & " DAYS, " &HOUR (B1-A1) & "hours," & MINUTE (B1-A1) & "minutes and" & SECOND (B1-A1) & "seconds"

You can also display the time difference with no zero values by embedding additional IF functions into the formula, like so:

IF (INT(B1-A1)>0, INT(B1-A1) & "days, ","") & IF (HOUR(B1-A1)>0, HOUR(B1-A1) & " hours, ","") & IF (MINUTE(B1-A1)>0, MINUTE(B1-A1) & " minutes and ","") & IF (SECOND(B1-A1)>), SECOND (B1-A1) & "seconds","")

Although long, this formula covers all the necessary areas to present time differences with no zero values. An alternative approach calculates the time difference by subtracting start times and end times before inputting the following time format into the cell: d "days," h "hours," m "minutes" and s "seconds". This second option is useful because results are output in normal time values that can be helpful for other time calculations. The results from the more complicated formula present information as a text value. The only hindrance to the custom time format is that it can't pass zero and non-zero values.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles