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

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:

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

Finally, we’ll calculate the difference between the dates of 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.

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.

Related Articles

• How to Count Date Occurrences in Excel
• Add Weeks to A Date in Excel [4 Fast & Simple Methods with Template]
• Excel formula to Calculate Age on a Specific Date
• Calculate Number of Days Between Two Dates in Excel (8 Quick Tricks)
• 3 Suitable Excel Formula to Count Days from Date