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.