How to Calculate Difference Between Two Times in Excel: 8 Suitable Methods

While calculating time differences in Excel, you have to be careful about the Number formats of individual time values. Before starting the calculation, make sure cells that contain time values are in a suitable format.

Method 1 – Simple Subtraction Formula to Calculate Difference Between Two Times

This sample dataset contains start and finish times for several tasks:

Steps:

• Add the following formula in Cell D5and press Enter.

• The result shows the time difference in ‘h:mm’ format along with AM/PM.

• However, the result is not in the desired Number format. So change the Number format of Cell D5. To do that, right-click on Cell D5 and click on Format Cells. Or you can use Ctrl + 1 shortcut to get the Format Cells.

• In the Format Cells dialog box, go to the Number tab, click on the Time option from Category, and click on the preferred time format from the Type section. Press OK.

• You will get the time difference as shown. Use the Fill Handle(+) tool to get the time differences for the rest of the values.

• We will get the below output which represents the time difference between two values.

⏩ Note:

• In the above method, I have applied the ‘h:mm’ time format in the result. If you want to have the time difference in hours only, use the Custom format ‘h’ from the Format Cells dialog.

• If you want to display the resulting time difference in hh:mm:ss format, go to the Format Cells window, from the Custom category, and choose ‘hh:mm:ss’ from the Type section.

Read More: How to Subtract Time and Convert to Number in Excel

Method 2 – Determine the Difference Between Two Times in Hours/Minutes/Seconds Separately

2.1. Hour Difference

Steps:

• Add the function in Cell D5 and press Enter.

• You will get the time difference between Cell C5and Cell B5 in hours.

The INT function rounds the time difference to the nearest integer. Then the result of the INT formula is multiplied by 24 as there are 24 hours in a day.

Read More: How to Calculate Time Difference in Numbers

2.2. Minute Difference

Steps:

• Add the following formula in Cell D5.

• Hit Enter. Excel will return the time difference in minutes.

2.3. Second Difference

Steps:

• Add the following formula in Cell D5and hit Enter.

• We will get the following result.

Method 3 – Excel TEXT Function to Calculate Difference Between Two Time Values

Steps:

• Add the following formula in Cell D5 and press Enter.

• We will get the time difference as shown below:

⏩ Note:

• If you want to calculate the time difference in hh:mm:ss format, use the following formula:

• If you need the time difference in hours only, use the following formula:

Read More: How to Subtract Hours from Time in Excel

Method 4 – Combine Excel HOUR, MINUTE & SECOND Functions to Calculate Minute Difference

Steps:

• Add the following formula in Cell D5and press Enter.

• Excel will output the hour difference between the times of Cell B5and Cell C5.

• Add the following formula in Cell E5and press Enter.

• The following output will be displayed.

• Add the following formula in Cell F5and press Enter.

• The following result will be displayed.

• To get the same result for the rest of the time, select the range D5:F5 and use the Fill Handle tool.

• It will give the output as shown below.

Read More: How to Subtract Minutes from Time in Excel

Method 5 – Excel MOD Function to Find Difference Between Two Specific Times

We can use the MOD function in Excel to calculate time differences between two specific values.

Steps:

• Add the formula in Cell D5.
• Hit Enter.

• The MOD function returns the time difference.

Read More: How to Subtract Military Time in Excel

Method 6 – Calculate the Difference Between Two Times Using the IF function

If the end time of a task is greater than the start time,  e.g., the start time of a task is 9:00 PM on day-1 and the end time is 5:00 AM on day-2. In such cases, a simple subtraction formula will return a string of hash (#) characters.

The IF function solves this problem.

Steps:

• Add the formula in Cell D5.

• The following result will be displayed.

Method 7 – Apply the NOW Function to Get the Difference Between Certain Times to Till Now

We can calculate the difference between a certain time and the current time using the NOW function.

Steps:

• Add the formula in Cell C5.

• We will get the time difference between the two times (start time and current time).

⏩ Note:

If the difference between the start time and the current time is greater than 24 hours, then we have to change the format of the resulting cell (d “days” h:mm:ss).

Method 8 – Excel VBA to Calculate the Difference Between Two Times

Steps:

• Go to Developer > Visual Basic to bring the VBA window.

• Go to Insert > Module.

• Add the following code in the new Module and run the code using theF5

Sub Difference_Two_Times()
Set Start_Times = Range("B5:B8")
Set Finish_Times = Range("C5:C8")
Set Difference_Times = Range("D5:D8")
For i = 1 To Start_Times.Rows.Count
Time1 = Start_Times.Cells(i, 1)
Time2 = Finish_Times.Cells(i, 1)
Total_Seconds = (Time2 - Time1) * 24 * 3600
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
Difference_Times.Cells(i, 1) = Total_Time
Next i

End Sub

• It will display the following output.

Download Practice Workbook

Related Articles

• [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
• How to Subtract and Display Negative Time in Excel
• How to Subtract Date and Time in Excel

<< Go Back to Subtract Time | Calculate Time | Date-Time in Excel | Learn Excel