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.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.