Calculate Number of Days Between Two Dates with VBA in Excel

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.

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.

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.

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.

Related Articles

• Excel Formula to Count Days from Date
• How to Calculate Expiration Date with Excel Formula
• How to Count Months from Date to Today by Using Excel Formula
  
• How to Calculate Years from Today in Excel
• How to Calculate Years Between Two Dates in Excel
• Calculate Years and Months Between Two Dates in Excel