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.

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

=Finish Time - Start Time

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

Simple Subtraction Formula to Calculate Difference Between Two Times

Steps:

  • Type the following formula in Cell D5and press Enter.
=C5-B5

Simple Subtraction Formula to Calculate Difference Between Two Times

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

Simple Subtraction Formula to Calculate Difference Between Two Times

  • However, the result is not in the desired Numberformat. 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.

Simple Subtraction Formula to Calculate Difference Between Two Times

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

Simple Subtraction Formula to Calculate Difference Between Two Times

  • You will get the time difference as shown. 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

  • 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:

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

Hour Difference

  • 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:

  • Type the following formula in Cell D5.
=(C5-B5)*24*60

Minute Difference

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


2.3. Second Difference

Steps:

  • Type the following formula in Cell D5and hit Enter.
=(C5-B5)*24*60*60

Second Difference

  • We will get the following result.


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

Steps:

  • Type the following formula in Cell D5 and press Enter.
=TEXT(C5-B5,"h:mm")

Excel TEXT Function to Get Difference Between Two Time Values

  • 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:
=TEXT(C5-B5,"h:mm:ss")
  • If you need the time difference in hours only, use the following formula:
=TEXT(C5-B5,"h")

Read More: How to Subtract Hours from Time in Excel


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

Steps:

  • HOUR function calculates time difference between hours: type the following formula in Cell D5and press Enter.
=HOUR(C5-B5)

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

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

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • MINUTE function calculates time difference between minutes: type the following formula in Cell E5and press Enter.
=MINUTE(C5-B5)

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • The following output will be displayed.

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • SECOND function calculates time difference between seconds: type the following formula in Cell F5and press Enter.
=SECOND(C5-B5)

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • The following result will be displayed.

Combine HOUR, MINUTE & SECOND Functions to Find Minute Difference

  • 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:

  • Type the formula inCell D5.
  • Hit Enter.
=MOD(C5-B5,1)

Excel MOD Function to Find Difference Between Two Specific Times

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

Calculate Difference Between Two Times Using IF function

The IF function solves this problem.

Steps:

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

Calculate Difference Between Two Times Using IF function

  • 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:

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

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

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


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

Steps:

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

Excel VBA to Calculate Difference Between Two Times

  • Go to Insert > Module.

Excel VBA to Calculate Difference Between Two Times

  • Type 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

Excel VBA to Calculate Difference Between Two Times

  • It will display the following output.

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


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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