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
```

**Download Practice Workbook**

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

**Calculate Number of Days Between Two Dates with VBA in Excel (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 **3** ways with **VBA**.

**Method 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 **CDate function** of **VBA** 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:** **How to Add/Subtract Years to a Date in Excel**

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

You can use the **DateDiff function** of **VBA** 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:** **How to Use DateDiff Function in Excel VBA (5 Examples)Â **

**Similar Readings:**

**How to Create a Day Countdown in Excel (2 Examples)****Use COUNTIFS with Date Range in Excel (6 Easy Ways)****How to Calculate Years Between Two Dates in Excel (2 Methods)****Add 7 Days to a Date in Excel (3 Methods)****How to Add 30 Days to a Date in Excel (7 Quick Methods)**

**Method 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 **1/1/2021**, and cell **C5** in the worksheet contains the ending date **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**.

**Related Content:**** Number of Months Between Two Dates in Excel: 5 Easy Ways to Find**

**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 columns **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**.

**Related Content:** **How to Calculate Overdue Days in Excel (3 Methods)**

**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 the cell** D4**.

Finally, weâ€™ll calculate the difference between the dates of 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**.

**Related Content:** **Excel Formula to Calculate Number of Days Between Today & Another Date (6 Quick Ways)**

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

**Conclusion**

So, these are the ways to use 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. And donâ€™t forget to visit our site **ExcelDemy** for more posts and updates.