While working with times with **VBA** in Excel, we often have to calculate the **time difference between two times**. In this article, I’ll show you how you can calculate the difference between two times with **VBA** in Excel. You’ll learn to calculate the time difference with proper examples and illustrations involving **Macro **and** User-Defined Functions**.

Calculate Time Difference in Excel VBA (Quick View)

```
Sub Time_Difference_by_Direct_Substitution()
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
Total_Seconds = (Time2 - Time1) * 24 * 3600
MsgBox Total_Seconds
Total_Minutes = Int(Total_Seconds / 60)
MsgBox Total_Minutes
Total_Hours = Int(Total_Seconds / 3600)
MsgBox Total_Hours
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)
MsgBox Total_Time
End Sub
```

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**2 Methods to Calculate Time Difference in Excel VBA (Step by Step Analysis)**

So, without further delay, let’s move to our main discussion. You can calculate the** number of days between two dates** in **2** ways with **VBA**.

**Method 1: Calculate Time Difference Between Two Times by Direct Subtraction**

First of all, you can calculate the **difference between two times** by direct substitution.

Let’s have two dates, **6:03:59 AM** and **7:05:10** **AM**.

First, we’ll use the **CDate function** of **VBA** to convert the strings to times.

```
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
```

Image 1.

Next, we’ll calculate the **difference between the two times** by direct substitution.

**⧪ Case 1: Calculating Total Seconds**

To calculate the difference between two times in total seconds, use:

```
Total_Seconds = (Time2 - Time1) * 24 * 3600
MsgBox Total_Seconds
```

Run the code, and it’ll display the time between the two times in full seconds.

**⧪ Case 2: Calculating Total Minutes**

To calculate the **difference between two times in total minutes**, use:

```
Total_Minutes = Int(Total_Seconds / 60)
MsgBox Total_Minutes
```

Run the code ( remove the line **MsgBox Total_Seconds **if you don’t want to see the total seconds) and it’ll display the time between the two times in full minutes.

**⧪ Case 3: Calculating Total Hours**

To calculate the difference between two times in total hours, use:

```
Total_Hours = Int(Total_Seconds / 3600)
MsgBox Total_Hours
```

Run the code (remove the lines **MsgBox Total_Seconds** and **MsgBox Total_Minutes** if you don’t want to see them) and it’ll display the time between the two times in full hours.

**⧪ Case 4: Calculating Hours, Minutes, and Seconds Together**

To calculate the** time difference** in hours, minutes, and seconds together, use:

```
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)
MsgBox Total_Time
```

Run the code (remove the lines **MsgBox Total_Seconds**, **MsgBox Total_Minutes, **and** MsgBox Total_Hours** if you don’t want to see them) and you’ll get the difference between the two times in hours, minutes, and seconds together.

Therefore, the complete **VBA** code will be:

**⧭ VBA Code:**

```
Sub Time_Difference_by_Direct_Substitution()
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
Total_Seconds = (Time2 - Time1) * 24 * 3600
MsgBox Total_Seconds
Total_Minutes = Int(Total_Seconds / 60)
MsgBox Total_Minutes
Total_Hours = Int(Total_Seconds / 3600)
MsgBox Total_Hours
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)
MsgBox Total_Time
End Sub
```

**⧭ Output:**

Run the code, and it’ll display the total seconds, total minutes, total hours, and the hours, minutes, and seconds together one by one.

**Read More:** **Excel formula to find difference between two numbers**

**Method 2: Calculate Time Difference Between Two Dates by DateDdiff Function of VBA**

You can use the **DateDfiff function** of **VBA** to calculate the difference between two times in Excel **VBA**.

Here also, we’ll use the **CDate function** to convert the strings to dates.

**⧪ Case 1: Calculating Total Seconds**

To calculate the difference between two times in total seconds, use:

```
Total_Seconds = DateDiff("s", Time1, Time2)
MsgBox Total_Seconds
```

Run the code, and it’ll display the difference between the two times in full seconds.

**⧪ Case 2: Calculating Total Minutes**

To calculate the difference between two times in total minutes, use:

```
Total_Minutes = DateDiff("n", Time1, Time2)
MsgBox Total_Minutes
```

Run the code ( remove the line **MsgBox Total_Seconds **if you don’t want to see the total seconds) and it’ll display the time between the two times in full minutes.

**⧪ Case 3: Calculating Total Hours**

To calculate the difference between two times in total hours, use:

```
Total_Hours = DateDiff("h", Time1, Time2)
MsgBox Total_Hours
```

Run the code (remove the lines **MsgBox Total_Seconds** and **MsgBox Total_Minutes** if you don’t want to see them) and it’ll display the time between the two times in full hours.

**⧪ Case 4: Calculating Hours, Minutes, and Seconds Together**

To calculate the time difference in hours, minutes, and seconds together, use:

```
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)
MsgBox Total_Time
```

Run the code (remove the lines **MsgBox Total_Seconds**, **MsgBox Total_Minutes, **and** MsgBox Total_Hours** if you don’t want to see them) and you’ll get the difference between the two times in hours, minutes, and seconds together.

Therefore, the complete **VBA** code will be:

**⧭ VBA Code:**

```
Sub Time_Difference_by_DateDiff_Function()
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
Total_Seconds = DateDiff("s", Time1, Time2)
MsgBox Total_Seconds
Total_Minutes = DateDiff("n", Time1, Time2)
MsgBox Total_Minutes
Total_Hours = DateDiff("h", Time1, Time2)
MsgBox Total_Hours
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)
MsgBox Total_Time
End Sub
```

**⧭ Output:**

Run the code, and it’ll display the total seconds, total minutes, total hours, and the hours, minutes, and seconds together one by one.

**Similar Readings**

**Calculate Positive or Negative Difference Between Two Numbers in Excel****How to Calculate Absolute Difference between Two Numbers in Excel****Pivot Table: Percentage Difference between Two Columns**

**Examples to Calculate Time Difference Between Two Times with Excel VBA**

We’ve learned to calculate the difference between two times using Excel **VBA**. Now, let’s explore a few examples of this.

**Example 1: Developing a Macro to Calculate Difference Between Two Columns of Times with Excel VBA**

Here we’ve got a data set with the **Names, Attending Times,** and **Leaving Times** of some employees of a company on a particular day.

Now we’ll develop a **Macro** to calculate the working time of each of the employees.

That is, we’ve to calculate the differences between the two times of the columns **C4:C13** and **D4:D13**.

**⧭ VBA Code:**

```
Sub Time_Difference()
Set Attending_Times = Range("C4:C13")
Set Leaving_Times = Range("D4:D13")
Set Working_Times = Range("E4:E13")
For i = 1 To Attending_Times.Rows.Count
Time1 = Attending_Times.Cells(i, 1)
Time2 = Leaving_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)
Working_Times.Cells(i, 1) = Total_Time
Next i
End Sub
```

**⧭ Output:**

Run this code. It’ll calculate the working time of each employee in the range **E4:E13**.

**Example 2: Creating a User-Defined Function to Calculate Time Difference Between Two Times with Excel VBA**

Now we’ll create a **User-Defined function** that’ll take two times as the arguments, and return the difference between them.

The **VBA** code will be:

**⧭ VBA Code:**

```
Function TimeDifference(Time1, Time2)
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)
TimeDifference = Total_Time
End Function
```

**⧭ Output:**

Select the cell **E4** from the worksheet and enter this formula:

**=TimeDifference(C4,D4)**

It’ll return the difference of times between the two times in cells **C4** and **D4**.

Now you can drag the **Fill Handle** to copy the formula to the rest of the cells.

**Things to Remember**

We’ve used the **Datediff function** of **VBA** in **method 2** of our process. But there’s almost a similar function in Excel called the **DATEDIF function**. But it can only calculate the **difference between two dates**, not two times.

**Conclusion**

So, these are the ways to use calculate the difference between two times with **VBA** in Excel. Hope the examples made everything clear for you. Do you have any questions? Feel free to ask us. And don’t forget to visit our site **ExcelDemy** for more posts and updates.