While working with dates with VBA in Excel, we often have to calculate the number of days between two dates. In this article, Iâ€™ll show you how you can calculate the number of days between two dates with VBA in Excel. Youâ€™ll learn to calculate the number of days with proper examples and illustrations involving *Macro* and *UDF.*

**Calculate Number of Days Between Two Dates with VBA in Excel (Quick View)**

```
Sub Number_of_Days_Between_Two_Dates_1()
Date1 = CDate("1/1/2021")
Date2 = CDate("6/6/2021")
Number_of_Days = Date2 - Date1
MsgBox Number_of_Days
End Sub
```

**Calculate Number of Days Between Two Dates with Excel VBA: 3 Methods**

Without further delay, letâ€™s move to our main discussion. You can calculate the number of days between two dates in 3 methods with **VBA**.

**1. Calculate Number of Days Between Two Dates by Direct Subtraction**

First of all, you can calculate the number of days between two dates by direct substitution.

Letâ€™s have two dates, *1/1/2021 *and *6//6/2021*. First, weâ€™ll use the VBA **CDate **function to convert the strings to dates. Next, weâ€™ll calculate the days between two dates by direct substitution.

So, the complete VBA code will be:

**â§ VBA Code:**

```
Sub Number_of_Days_Between_Two_Dates_1()
Date1 = CDate("1/1/2021")
Date2 = CDate("6/6/2021")
Number_of_Days = Date2 - Date1
MsgBox Number_of_Days
End Sub
```

**â§ Output:**

Run the code, and itâ€™ll display the days between the two dates, *1/1/2021 *and *6/6/2021. *Itâ€™s *156.*

**Read More:Â **Excel Calculates Difference Between Two Dates in Days

**2. Calculate Number of Days Between Two Dates by the DateDiff Function of VBA**

You can use the **VBA DateDiff **function to calculate the number of days between two dates by Excel VBA.

Here also, weâ€™ll use the **CDate **function to convert the strings to dates. Along with these, weâ€™ll need another variable called **Difference_Format** that will contain the format of the differences. Here itâ€™s *dÂ (Days)*.

So the complete VBA code will be:

**â§ VBA Code:**

```
Sub Number_of_Days_Between_Two_Dates_2()
Difference_Format = "d"
Date1 = CDate("1/1/2021")
Date2 = CDate("6/6/2021")
MsgBox DateDiff(Difference_Format, Date1, Date2)
End Sub
```

**â§ Output:**

Run the code, and itâ€™ll again display the days between the two dates, *1/1/2021 *and *6/6/2021. *Itâ€™s *156.*

**Read More:Â **Excel Formula to Calculate Number of Days Between Today and Another Date

**3. Calculate Number of Days Between Two Dates by the DATEDIF Function of Excel**

You can also calculate the number of days between two dates by the **DATEDIF** function of Excel.

But you can only use this method if the dates are taken from Excel cells.

For example, letâ€™s cell **C3** in the worksheet contains the starting date of *1/1/2021*, and cell **C5** in the worksheet contains the ending date of *6/6/2021*.

And we want to get the difference between these two dates in cell **B9**.

Now we can develop a VBA code using the **DATEDIF** function of Excel. But to use the **DATEDIF** function, weâ€™ll need one more argument called the **Difference_Type**.

Here itâ€™s *d*. Weâ€™ve put it in cell **C7**.

Now weâ€™ll develop the VBA code. The code will be:

**â§ VBA Code:**

```
Sub Number_of_Days_Between_Two_Dates_3()
Range("C9").Formula = "=DATEDIF(C3,C5,C7)"
End Sub
```

**â§ Output:**

Run the code. Itâ€™ll show the difference between the two dates in cell **C9**. Itâ€™s *156*.

**Read More:Â **How to Find Number of Weeks Between Two Dates in Excel

**Examples to Calculate Number of Days Between Two Dates with Excel VBA**

Weâ€™ve learned to calculate the number of days between two days using Excel VBA. Now, letâ€™s explore a few examples of this.

**Example 1: Developing a Macro to Calculate the Number of Days Between Two Columns of Dates with Excel VBA**

Here weâ€™ve got a data set with the *Names, Joining Dates, *and *Todayâ€™s Dates* of some employees of a company called *Jupyter Group*.

Now weâ€™ll develop a *Macro *to calculate the service days of each of the employees.

Weâ€™ve to calculate the number of days between each date of the range **C4:C13** and **D4:D13**.

**â§ VBA Code:**

```
Sub Calculate_Service_Days()
Set Starting_Dates = Range("C4:C13")
Set Present_Dates = Range("D4:D13")
Set Service_Days = Range("E4:E13")
For i = 1 To Starting_Dates.Rows.Count
Â Â Â For j = 1 To Starting_Dates.Columns.Count
Â Â Â Â Â Â Â Service_Days.Cells(i, j) = CDate(Present_Dates.Cells(i, j)) - CDate(Starting_Dates.Cells(i, j))
Â Â Â Next j
Next i
End Sub
```

**â§ Output:**

Run this code. Itâ€™ll calculate the service days of each employee in the range **E4:E13**.

**Read More:Â **How to Calculate Number of Months Between Two Dates in Excel

**Example 2: Creating a User-Defined Function to Calculate Number of Days Between Two Dates with Excel VBA**

Now weâ€™ll create a *User-Defined* function thatâ€™ll take two dates or two ranges of dates as the arguments, and return the difference (s) between them.

The VBA code will be:

**â§ VBA Code:**

```
Function Days_Difference(Date1, Date2)
If VarType(Date1) <> 8204 And VarType(Date2) <> 8204 Then
Â Â Â Days_Difference = CDate(Date2) - CDate(Date1)Â
Â Â
ElseIf VarType(Date1) = 8204 And VarType(Date2) <> 8204 Then
Â Â Â Dim Output1() As Variant
Â Â Â ReDim Output1(Date1.Rows.Count - 1, Date1.Columns.Count - 1)
Â Â Â For i = 1 To Date1.Rows.Count
Â Â Â Â Â Â Â For j = 1 To Date1.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Output1(i - 1, j - 1) = CDate(Date2) - CDate(Date1.Cells(i, j))
Â Â Â Â Â Â Â Next j
Â Â Â Next i
Â Â Â Days_Difference = Output1Â Â Â
Else
Â Â Â Dim Output2() As Variant
Â Â Â ReDim Output2(Date1.Rows.Count - 1, Date1.Columns.Count - 1)
Â Â Â For i = 1 To Date1.Rows.Count
Â Â Â Â Â Â Â For j = 1 To Date1.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Output2(i - 1, j - 1) = CDate(Date2.Cells(i, j)) - CDate(Date1.Cells(i, j))
Â Â Â Â Â Â Â Next j
Â Â Â Next i
Â Â Â Days_Difference = Output2
End If
End Function
```

**â§ Output:**

First, weâ€™ll calculate the difference between two single days.

Select a cell from the worksheet and enter this formula.

`=Days_Difference("5/1/2013","3/8/2022")`

Itâ€™ll return the difference of days between these two dates.

Now weâ€™ll calculate the difference between a range of dates and a single date.

Select a range of cells and enter this formula.

`=Days_Difference(C4:C13,D4)`

Then press **CTRL + SHIFT + ENTER** (array formula). Itâ€™ll return the difference of each date of the range** C4:C13** from the date of cell** D4**.

Finally, weâ€™ll calculate the difference between the dates of the two ranges.

Select a range of cells and enter this formula.

`=Days_Difference(C4:C13,D4:D13)`

Then press **CTRL + SHIFT + ENTER** (array formula). Itâ€™ll return the difference of each date of the range** C4:C13** from the dates of the range** D4:D13**.

**Things to Remember**

In this article, I showed you how you can calculate the number of days between two dates only. If you want to calculate the number of workdays, you can use the **NETWORKDAYS **function or the **NETWORKDAYS.INTL **function of Excel.

**Download Practice Workbook**

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

**Conclusion**

So, these are the ways to calculate the number of days between two dates with VBA in Excel. Hope the examples made everything clear for you. Do you have any questions? Feel free to ask us. Donâ€™t forget to visit our website for more posts and updates.