Excel Formula: Calculate difference between two times

 

To calculate the difference between two times, this tutorial provides a simple formula and also explains how to use it in Excel.

If you want to follow along with this tutorial, please download the example spreadsheet.

Generic formula:

IF(time2>time1,time2-time1,time1-time)

Arguments

Time1, time2: the two times you want to calculate the difference.

Return value

This formula returns a numeric value, between 0-1.

How this formula work

To calculate the difference between time 1 and time 2 in cell B3 and B4, please use below formula:

=IF(C3>B3,C3-B3,B3-C3)

Press Enter key to get the number.

Format the cell to a time format you want by pressing Ctrl + 1 to open the Format Cells dialog, then choose a time format.

Then drag autofill handle over cells that need to calculate difference of times.

Explanation

IF function: Perform a simple logical test depending on the comparison result, and it returns one value if a result is TRUE, or another value if result is FALSE.

As if the difference between two times is a negative value, Excel will display it as #####. For avoiding this, we use IF function. Supposing time2 is larger than time1, then it displays the result as time2 -time1, if does not match this condition, it shows the result as time1-time2.

Note:

This formula cannot handle durations greater than 24 hours.

---

Relative Formulas

• Calculate days remaining in month
  In this tutorial, it provides a formula to quickly calculate the days remaining in month in Excel.
• Calculate days remaining from today
  Sometimes, you may want to know how many days remaining until expiration from today of each food product. In Excel, there is a formula that can help you to quickly get the remaining days.
• Calculate Days Remaining Between Two Dates
  Here, this tutorial provides a formula to quickly calculate the left days between two dates.
• Calculate Network Time With Break In A Timesheet
  It provides the formula which uses the MOD function to calculate the network time with breaks in Excel.

Relative Functions

• Excel IF Function
  Test for specified conditions, then returns the corresponding values
• Excel VALUE Function
  Convert text to number.
• Excel MONTH Function
  The MONTH is used to get the month as integer number (1 to 12) from date.
• Excel DAY Function
  DAY function gets the day as a number (1 to 31) from a date
• Excel YEAR Function
  The YEAR function returns the year based on the given date in a 4-digit serial number format.