Excel

Formula for subtracting time in Excel between two times

formula for subtracting time in excel

Subtraction is one of the most basic things that we learned in elementary school. Similarly, Excel’s number subtraction is an easy task, and subtracting time is also an easy task.

Let’s say that you work on your own multiple projects, and want to know how much time you spent on each project. Microsoft Excel offers you various ways to calculate time in Excel. In this guide, you will find the various formula for subtracting time in Excel.

To find the elapsed time (difference between two times), follow these procedures.

Formula for subtracting time in Excel between two times to get the time difference

To determine the spending time, we frequently need to calculate the difference time between cells. There are several ways or formulas for subtracting time in Excel that we are going to discuss below.

Formula 1: Subtracting time with a simple formula

You can use a simple formula to subtract time between two-time cells.
Follow these procedures

Formula

=(End Time-Start Time)

Firstly, select the result cell (C2) and then type the formula:

=(B2-A2)

subtracting time simple formula

And then press Enter to see the result.

subtracting time

You probably know that in the internal Excel system, excel stores times in the format of a decimal number (that’s our result). So here we need to change the time formatting to display the spending times as hours, minutes & seconds properly.

1. For that purpose, select the result cell of the decimal numbers (C2 for our example) then right-click the cell and select the Format Cells in the context menu. Or press the key shortcut Ctrl+1, a Format Cells dialog box will appear.

2. In the dialog box, select the type of time format under the Time category then click OK.

apply the time format

3. The decimal numbers cell has changed as hours, minutes & seconds.

4. And finally, use the Fill Handle to see the results in all the expected cells.

subtracting time with simple formula

Formula 2: Subtracting time with the TEXT Function

You can subtract time between two times in Excel using the TEXT Function. Normally, the Text Function converts any numbers into the text string within a worksheet with one of the following formats.

Time FormatResult
hDisplay only hours (such as 3).
h:mmDisplay hours and minutes (such as 3:20).
h:mm:ssDisplay hours, minutes, and seconds (such as 3:20:50).

Syntax

TEXT(value, format _ text)

Generic formula

=TEXT(End Date – Start Date, Format)

  • To subtract only hours between two times: =TEXT(B2-A2, “h”)
  • To subtract hours and minutes between two times: =TEXT(B2-A2, “h:mm”)
  • To subtract hoursminutes, and seconds between two times: =TEXT(B2-A2, “h:mm:ss”)
subtract with text function

Noto
TEXT formula displays the #VALUE! error, if the result is shown as a negative number.

Formula 3: Subtracting time with the MOD Function

You can use the MOD function to subtract time between the two times in Excel. The MOD function basically helps us to find the remainder after dividing a number (dividend) by another number (divisor).

Syntax

=MOD(number,divisor)

Generic formula

=MOD(End Date – Start Date,1)

Select the result cell (C2) first and then type the formula

=MOD((B2-A2),1))

subtracting with mod function

And then press Enter to see the result.

Here again, need to change the Time Format.

Finally, use the Fill Handle to see the results in all the expected cells.

subtract time with mod function

Formula 4. Subtracting time in hours, minutes, or seconds

When you subtract time, Excel gives a decimal number that represents the time subtraction.

Since each integer represents a day, the decimal part represents a fraction of a day that can easily be converted into hours, minutes, or seconds.

You can use the following calculations to get the subtracting time in a single time unit (hours, minutes, or seconds).

Subtracting Hours between two times

To subtract hours between two times, use this formula:

=(End time – Start time) * 24

subtraction hours

Use the INT function to round the value down to the closest integer to get the number of complete hours:

=INT((B2-A2) * 24)

INT function to round the value

Subtracting Minutes between two times

To subtract minutes between two times, use this formula:

=(End time – Start time) * 1440

To get only minutes unit between two times, multiply the time difference by 1440, that’s the number of minutes in a day (24 hours * 60 minutes = 1440).

=(B2-A2) * 1440

subtracting minutes between two times

Subtracting Seconds between two times

To subtract seconds between two times, use this formula:

=(End time – Start time) * 86400

To get only seconds unit between two times, multiply the time difference by 86400, that’s the number of seconds in a day (24 hours * 60 minutes * 60 seconds = 86400).

=(B2-A2) * 86400

subtracting seconds between two times

Formula 5. Subtracting time into one unit (hours, minutes, or seconds) ignoring others

Use one of the following functions to find the subtraction between two times in a specific time unit ignoring others.

CalculationFormula
Hours=HOUR(B2-A2)
Minutes=MINUTE(B2-A2)
Seconds=SECOND(B2-A2)

subtracting time into one unit

Formula 6. Subtracting time from the start to now to get the Elapsed Time

Another simple formula to subtract elapsed time from the start to now is the NOW function which returns the current date and time from the start time.

Formula 

=NOW()-A2

In this example, Column B returns the following results because we’ve applied the 13:30:55 time format.

subtract elapsed time

Our result shows only time because of using the 13:30:55 time format, using the d “days” h:mm:ss like in the following screenshot to get the result with the days.

subtract elapsed time and days from now

Formula 7: Subtracting time with the IF Function

IF Function can also be used to subtract time between two-time cells. If the logic is correct, the IF Function returns a value. Otherwise, it will return a different value.

If start and end times span midnight, then you need to adjust by the IF Function that’s explained below. 

You probably know the end time is actually less than the start time it returns a negative value that’s will display as hash (######) characters. The below formula will help you get time subtraction without any hash (######) or negative sign.

Generic formula 

=IF(end>start, end-start, 1-start+end)

Formula

=IF(B2>A2,B2-A2,1-A2+B2)

subtract time with if function

Change the Time Format, and get results in all the expected cells using the Fill Handle.

Formula 8: Display subtracting time as “XX days, XX hours, XX minutes, and XX seconds.”

To get corresponding time units, you can use the HOUR, MINUTE, and SECOND functions, and the INT function to compute the difference in days. This formula may be most user-friendly for subtracting time in Excel between two times to get the time difference displayed as “XX days, XX hours, XX minutes, and XX seconds.”

time subtracting formula in excel

If you want to hide zero values in this subtracting time, then you can use the below formula.

=IF(INT(B2-A2)>0, INT(B2-A2) & ” days, “,””) & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & ” hours, “,””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & ” minutes and “,””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)

time subtracting formula ignores zero values

Results Showing Hash (###) Instead of Date/Time (Reasons + Fix)

Sometimes Excel is displaying the hash (####) symbols in our format cell when it can’t find enough space to show the result in the cell, then it can be solved by increasing column width.

Similarly, you find the result as hash (####) in our time subtraction cells due to negative time. In this case, you can show the negative time will start showing by setting the 1904 date system.

How to change Excel date system to 1904 date system

Switching to the 1904 date system is the quickest and easiest method to show negative time normally.

To do this, go to the File tab and click Options then select Advanced, scroll down to go to the When calculating this workbook section, and check the Use 1904 date system box.

changing to the 1904 date system

Result (1900 date system vs 1904 date system).

negative times in excel

Combine functions so that Hash (#######) is not displayed.

Combine with the IF function, TEXT function, and ABS function to display negative times properly.

Following formula:

=IF(B2-A2>0, B2-A2, “-” & TEXT(ABS(B2-A2),”h:mm”))

combine functions for negative value

You Might Also Like

No Comments

Leave a Reply

back to top