Calculate the number of days between two dates in Excel VBA

Steps to Create the Code:

1. Open the VBA Editor:
   • In Excel, press Alt + F11 to open the VBA editor.
2. Add a Module:
   • In the VBA editor, go to the Insert menu and choose Module to add a new module to your project.
3. Write the VBA Code:

Here is the complete VBA code to calculate the number of days between two dates:

VBA Code:

Sub CalculateDaysBetweenDates()
    ' Declare variables
    Dim date1 As Date
    Dim date2 As Date
    Dim days As Long
    Dim message As String
    ' Ask the user to enter the dates via an InputBox
    On Error GoTo DateError
    date1 = CDate(InputBox("Enter the first date (format: dd/mm/yyyy):"))
    date2 = CDate(InputBox("Enter the second date (format: dd/mm/yyyy):"))   
    ' Calculate the difference in days
    days = DateDiff("d", date1, date2)  
    ' Display the result in a message box
    message = "The number of days between " & date1 & " and " & date2 & " is: " & days
    MsgBox message, vbInformation, "Result"
    Exit Sub
DateError:
    MsgBox "Error with the date format. Please enter valid dates in dd/mm/yyyy format.", vbCritical, "Error"
End Sub

Explanation of the Code:

1. Declaring Variables:
   • date1 and date2 store the two dates entered by the user.
   • days is a Long type variable that will store the number of days between the two dates.
   • message is a String variable used to prepare the message that will be displayed to the user.
2. Getting Dates from User:
   • The user is prompted to enter the two dates via InputBox dialogs. Each date is converted to a Date type using the CDate function.
3. Calculating the Difference in Days:
   • The DateDiff function is used to calculate the difference between date1 and date2. The « d » parameter specifies that we want the difference in days.
   • The result is stored in the days variable.
4. Displaying the Result:
   • The result is displayed in a message box (MsgBox). The message shows both entered dates and the number of days between them.
5. Error Handling:
   • If the user enters an invalid date format, an error is triggered, and the user is shown an error message. This is handled with the On Error GoTo statement and the DateError label.

Example of Usage:

1. When you run the macro, a dialog box will appear asking you to enter the first date.
2. A second dialog box will appear for you to enter the second date.
3. Once the dates are entered, the macro will calculate the number of days between them and display a message with the result.

Possible Improvements:

• Automatic Date Entry: You can modify the code to fetch the dates directly from specific cells in the worksheet.
• Advanced Error Handling: You can add more checks to ensure the dates are entered correctly before performing the calculation.

Example of Code with Dates from Cells:

Sub CalculateDaysBetweenDatesFromCells()
    ' Declare variables
    Dim date1 As Date
    Dim date2 As Date
    Dim days As Long
    Dim message As String
    ' Get the dates from cells A1 and B1
    date1 = Cells(1, 1).Value  ' Cell A1 for the first date
    date2 = Cells(1, 2).Value  ' Cell B1 for the second date   
    ' Check if the cells contain valid dates
    If IsDate(date1) And IsDate(date2) Then
        ' Calculate the difference in days
        days = DateDiff("d", date1, date2)      
        ' Display the result in a message box
        message = "The number of days between " & date1 & " and " & date2 & " is: " & days
        MsgBox message, vbInformation, "Result"
    Else
        MsgBox "Please enter valid dates in cells A1 and B1.", vbCritical, "Error"
    End If
End Sub

In this example, the dates are taken from cells A1 and B1. If either of the cells does not contain a valid date, an error message will be shown.

Summary:

• The first code uses InputBox to allow the user to input the dates manually.
• The second code retrieves the dates from cells A1 and B1 on the worksheet.
• Both versions calculate the number of days between the two dates and display the result in a message box.