# Calculate Number of Days Between Two Dates with VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelorâ€™s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF