How to Calculate Time Difference in Excel

In Excel, there are a lot of functions to deal with date and time. But there is no specific function that you can use to get the difference between times values.

In Excel, calculating the difference between two times can be tricky. Times are handled internally as numbers between 0 and 1.

The easiest way to calculate a time difference is to subtract one time from the other.

It works because Excel stores date in integers and times in decimal values. Only the formatting of an Excel cell causes the cell’s contents to be displayed as a date or time rather than a simple number.

Therefore, when you want to calculate the difference between two times, you can subtract the time values similar to subtract any other numeric values.

Formatting Cells as Times

In Excel, when you calculate a time difference, you may want the result to be displayed as a time.

If it is not displayed as a time, you can format the cell to have a ‘Time’ format, using any of the following methods:

Method 1: Format Cells Using the Excel Ribbon Options

The easiest way to apply a simple ‘Time’ format is to select the cell formatted and then selects the Time option from the ribbon drop-down menu. This is found in the ‘Number’ group on the Home tab of the ribbon, as seen below:

Method 2: Format Cells Using the ‘Format Cells’ Dialog Box

If you require a greater selection of time formats, you may prefer to apply cell formatting using the ‘Format Cells’ dialog box as follows:

Step 1: Select the cell to be formatted.

Step 2: Open up the ‘Format Cells’ dialog box.

Step 3: Clicking on the dialog box launcher in the ‘Number’ grouping on the Excel ribbon’s Home tab.

Or you can use the keyboard shortcut Ctrl + 1.

Step 4: Ensure the Number tab of the ‘Format Cells’ dialog box is selected.

Step 5: From the Category list, select the option Time.

NOTE: If you want a time other than the default, you can then select one of the list options on the right-hand side of the ‘Format Cells’ dialog box.

Step 6: Click Ok to apply the selected format and close the ‘Format Cells’ dialog box.

Example

Cell C2 of the following spreadsheet shows a simple example of how to calculate a time difference by subtracting the time in cell A2 from cell B2.

In the example above, the time in cell A2 is internally represented by the number, 0.375 and the time in cell B2 is internally represented by the number 0.270833333. Subtracting these two numbers gives the result 0.395833333, which formatted as a time, is 09:30:00 (i.e., 9 hours 30 minutes and 0 seconds).

A time value is a numeric value formatted as time, but when you deduct one value from another, it returns a decimal number, and you need to format it again as time.

When you format that result value, it shows those values as hours and minutes (starting from 00:00) instead of showing the actual difference in hours and minutes.

So if you have a difference of 9 hours and 30 minutes, it will show it as 09:30 AM.

Ways to Calculate Time Difference

Here are the six different methods which you can use to get the difference between time values, such as:

1. Get difference in hours and minutes.
2. Get different in hours in numbers.
3. Get different in completed hours.
4. Get difference in minutes.
5. Get difference in seconds.
6. Calculate difference as a negative value.

Below is the step by step explanation of all six methods to calculate time difference with examples:

1. Get difference in Hours and Minutes

It is one of the most common ways to get the difference between two times.

Step 1: Let’s suppose you have a start and end time where you need to get the difference of both.

Step 2: First, you can deduct the end time from the start time, and you’ll get the difference as follows.

Step 3: Now, you need to convert this time into hours and minutes.

• Select the cell and open format option (Ctrl + 1).
• And then, select the “Time” category and select the second time format.
• Click on the ok button.

Once you do this, it will convert the time difference format into hours and minutes instead of the normal time format.

2. Get Different in Hours in Numbers

It is not a common way to get time difference but still useful and important if you want to differentiate between the start and end time in hours but not in normal numbers.

For example, if the difference between start and end time is 4 hours and 30 minutes, it should show it as 4.5 hours. Here is the formula which is used to show this format of the difference of time.

Once you enter this formula, it returns the hours in numbers and minutes in decimals.

Excel stores date and time in numbers, and when you deduct end time from the start time, it returns a decimal.

And when further you multiply it by 24, it converts that decimal into the number of hours and minutes.

3. Get Different in Completed Hours

You can also calculate the completed hours between two times with this simple formula.

This formula is used in the second method. The only difference is here we have wrapped it in INT.

INT function returns the integer part decimal by rounding it down. Therefore, when you enter this formula, first of all, it returns a decimal number for hours, and then INT removes the fractional part and gives you completed hours.

4. Get Difference in Minutes

It sometimes happens that you need to track the time difference in minutes instead of hours. In this case, use the following formula.

In this formula, after deducting start time from end time, we have multiplied it by 1440, which is the total number of minutes we have in 24 hours.

And when you enter this formula, you’ll get the difference between two values in minutes.

5. Get Difference in Seconds

If you have seconds in your time values, you use the below formula to get the total complete seconds between the start and end time.

Here 86400 is the total second in 24 hours, and when you enter this formula, it will return the difference in start and end time in seconds.

6. Calculate Difference as a Negative Value

In all the above examples, we have the end time, which is greater than the start time. Suppose you have two time values where the end time is greater than the start time.

For example, if the start time is 11:00 PM on day 1, the end time is 3:00 AM on day 2.

The end time is less than the start time, and the simple subtract formula will not work and return a negative value, which will cause Excel to display an error in the form string of hash characters (i.e., ########).

So, there is a formula to avoid this issue:

In this formula, you have a condition if the end time is greater than the start time, then it will simply deduct the end time from the start time; otherwise, firstly, it will deduct start time from 1 and then add it into the end time.

By subtracting the start time from 1, you will get the total time for day 1, and when you add it to the end time, you’ll get the total time of day 1 and day 2, which is the real difference.

Or change Excel Date System to 1904 date system. This is the fastest and easiest way to display negative time normally (with a minus sign) is by switching to the 1904 date system.

• Click on the office button.
• Go to the Excel Options.
  
• Then select the Advanced option and scroll down to the When calculating this workbook sectionand put a tick in the Use 1904 date system
  
• Click on the Ok button, and now negative times will be displayed correctly, like negative numbers as follows.