How to Calculate Difference Between Two Times in Excel?

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel, we often have to calculate the difference between two times. Calculating time differences is a very common operation in Excel. Luckily, there are various ways in Excel to find out time differences and most of them are very simple. This article will guide you in determining the differences between two values of time in Excel.


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. So, before starting the calculation, make sure cells that contain time values are in a suitable format. Let’s start the discussion and learn about all the suitable methods to find differences between times.

1. Simple Subtraction Formula to Calculate Difference Between Two Times

First of all, I will use the very basic subtraction formula to calculate the differences between the two times. The basic subtraction formula is:

=Finish Time - Start Time

Suppose, we have the below dataset containing start and finish times for several tasks as below:

Simple Subtraction Formula to Calculate Difference Between Two Times

Now I will calculate, the differences between the above times using the following steps:

Steps:

  • Type the below formula in Cell D5 and press Enter.
=C5-B5

Simple Subtraction Formula to Calculate Difference Between Two Times

  • As a result, here will receive the below output. The below result shows the time difference in ‘h:mm’ format along with AM/PM.

Simple Subtraction Formula to Calculate Difference Between Two Times

  • However, the above result is not in the desired Number format of ours. So, we will 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 dialog.

Simple Subtraction Formula to Calculate Difference Between Two Times

  • When the Format Cells dialog appears, 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 after that.

Simple Subtraction Formula to Calculate Difference Between Two Times

  • Consequently, you will get the time difference as shown in the below screenshot. Next, use the Fill Handle (+) tool to get the time differences for the rest of the values.

Simple Subtraction Formula to Calculate Difference Between Two Times

  • At last, 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.

  • Similarly, 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


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

Here, I will calculate the difference between the two times and represent that in hours, minutes, and seconds.

2.1. Hour Difference

Initially, I will determine the hour difference between the two times. Here, I will apply the INT function to calculate the time difference.

Steps:

  • Type the below function in Cell D5. Next press Enter.
=INT((C5-B5)*24)

Hour Difference

  • As a consequence, you will get the time difference between Cell C5 and Cell B5 in hours.

Here, 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

This time I will calculate the time difference in minutes. Here, the time difference between the two times is multiplied by 24 and 60 as there are 60 minutes in each hour of the day.

Steps:

  • First, type the below formula in Cell D5.
=(C5-B5)*24*60

Minute Difference

  • Once you hit Enter, Excel will return the time difference in minutes.


2.3. Second Difference

Now I will compute the second difference between the two times. To do that follow the below steps.

Steps:

  • Type the following formula in Cell D5 and hit Enter. To calculate the time difference in seconds, we did multiply it by 24, 60, and 60. This is because there are 60 seconds in each minute of a day.
=(C5-B5)*24*60*60

Second Difference

  • Consequently, we will get the below result.


3. Excel TEXT Function to Calculate Difference Between Two Time Values

You can use the TEXT function in Excel to calculate the difference between two times. Follow the below steps to perform the task.

Steps:

  • First I will determine the time difference in ‘h:mm’ format. Type the below formula in Cell D5. Then press Enter.
=TEXT(C5-B5,"h:mm")

Excel TEXT Function to Get Difference Between Two Time Values

  • As a result, we will get the time difference as below:

Note:

  • If you want to calculate the time difference in hh:mm:ss format, use the following formula:
=TEXT(C5-B5,"h:mm:ss")
  • Similarly, if you need the time difference in hours only, you can use the below formula:
=TEXT(C5-B5,"h")

Read More: How to Subtract Hours from Time in Excel


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

We can calculate the hour, minute, and second portion of a time difference using the combination of Excel functions. For instance, I will use the combination of HOUR, MINUTE, and SECOND functions to find out each portion of time. Follow the below instructions to complete the task.

Steps:

  • Firstly, I will find out the hour portion of the time difference between two specific times using the HOUR function. To do that, type the below formula in Cell D5 and press Enter.
=HOUR(C5-B5)

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • Consequently, Excel returns the hour difference between the times of Cell B5 and Cell C5.

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • Secondly, using the MINUTE function I will find out the portion of the minute difference between times. Type the following formula in Cell E5.
=MINUTE(C5-B5)

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • After entering the above formula, we will get the below output.

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • Thirdly, I will calculate the second portion of the time difference by applying the SECOND function. To do that, type the below formula in Cell F5.
=SECOND(C5-B5)

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • Upon entering the formula, we will get the below result.

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

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

  • Finally, here is the output we receive.

Read More: How to Subtract Minutes from Time in Excel


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:

  • Type the below formula in Cell D5.
  • Next, hit Enter.
=MOD(C5-B5,1)

Excel MOD Function to Find Difference Between Two Specific Times

  • As a consequence, the MOD function returns the time difference.

Read More: How to Subtract Military Time in Excel


6. Calculate the Difference Between Two Times Using the IF function

Did you ever think about what happens if the end time of a task is greater than the start time? I will explain further. Suppose,  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.

Calculate Difference Between Two Times Using IF function

However, the IF function solves our problem. Let’s follow the below steps to do the task.

Steps:

  • Type the below formula in Cell D5.
=IF(B5>C5,C5+1,C5)-B5

Calculate Difference Between Two Times Using IF function

  • As a result, you will get the below result.

Here, the IF function checks whether the start time is greater than the finish time. If the finish time is less than the start time, the formula adds 1 to the finish time, otherwise not. Finally, the start time is subtracted from the finish time.


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:

  • Type the below formula in Cell C5.
=NOW()-B5

Apply NOW Function to Get Difference Between Certain Time to Till Now

  • Upon entering the formula we will get the time difference between the two times (start time and current time).

Apply NOW Function to Get Difference Between Certain Time to Till Now

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).


8. Excel VBA to Calculate the Difference Between Two Times

You can determine the difference between two specific times using Excel VBA. Suppose we have the below dataset containing the start and finish times of some tasks. My times are in hh:mm:ss format.

Excel VBA to Calculate Difference Between Two Times

Now I will apply VBA to find the difference in times for the above data.

Steps:

  • First, go to Developer > Visual Basic to bring the VBA window.

Excel VBA to Calculate Difference Between Two Times

  • Then go to Insert > Module.

Excel VBA to Calculate Difference Between Two Times

  • After that, type the below code in the new Module and run the code using the F5 key.
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

Excel VBA to Calculate Difference Between Two Times

  • At last, upon running the code, we got the below output.

Read More: How to Subtract 30 Minutes from a Time in Excel


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several methods to open two Excel files separately elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo