Excel VBA UserForm: Difference Between Two Dates

How we are going to develop this project(The KEY steps) :

Let me explain the key steps to develop this UserForm to find difference between two dates project.You can see the design of the UserForm in the following section.

Step 1: Create UserForm: We have to first create the UserForm from the Insert menu in VBA Editor.
Step 2: Place toolbox controls on the created UserForm: Place required controls on the UserForm. You can drag the controls from the toolbox and drop controls on the UserForm. You can find detailed UserForm design details in the following section.
Step 3: Set properties and alignment of all controls: Properties of each control has to be set. It is shown in the design section.
Step 4: We are going to create procedures in the VBA editor window. Please find the following approach for the same.
Step 4.1: TurnOff screen update and Events: We are temporarily avoiding screen flickering and events triggering in the application.
Step 4.2: Variable Declaration: We are declaring required variables which are using in our procedure.
Step 4.3: Input Values: Assign input values to a variables.
Step 4.4: Calculate difference between two dates using ‘DateDiff’ Excel VBA function.
Step 4.5: Turn On screen update and Events: Let’s reset the screen update and events of application.

Design of the UserForm to Calculate difference between two dates:

Now, let us see the design of the UserForm and each control its properties and their values:

By changing or setting all the above properties and values of each control on the form will be looking like below.

Code and Explanation:

Step 1: Declaring variables which are using in the entire project.

'Variable Declaration
    Dim dtTdy As Date, dtDob As Date

Step 2: Disable Screen Updating is used to stop screen flickering and Disable Events is used to avoid interrupted dialog boxes or popups.

Application.ScreenUpdating = False

Step 3: Calculations for difference between two dates.

'calculating difference between two dates in days
lblDays.Caption = "Days difference is: " & DateDiff("d", dtDob, dtTdy)

'calculating difference between two dates in months
lblMnts.Caption = "Months difference is: " & DateDiff("m", dtDob, dtTdy)

'calculating difference between two dates in years
lblYrs.Caption = "Years difference is: " & DateDiff("yyyy", dtDob, dtTdy)

'calculating difference between two dates in quarter
lblQtrs.Caption = "Quaters difference is: " & DateDiff("q", dtDob, dtTdy)

'calculating difference between two dates in weeks
lblWks.Caption = "Weeks difference is: " & DateDiff("ww", dtDob, dtTdy, vbSunday)

Step 4:Increase UserForm height.

frmDateDiff.Height = 217

Step 5: Enable Screen Updating. Dont forget to enable screen update.

Application.ScreenUpdating = True

Final VBA Module Code(Macro):

Please find the following procedures to create UserForm and find difference between two dates.
Double click on the Userform(frmDateDiff) and add the following code to it.

Private Sub cmdCal_Click()

    Application.ScreenUpdating = False
    
    'Variable Declaration
    Dim dtTdy As Date, dtDob As Date
    
    'Input values(dates)
    dtDob = DTPkr1.Value: dtTdy = DTPkr2.Value
    
    'Finding difference between two dates
    lblDays.Caption = "Days difference is: " & DateDiff("d", dtDob, dtTdy)
    lblMnts.Caption = "Months difference is: " & DateDiff("m", dtDob, dtTdy)
    lblYrs.Caption = "Years difference is: " & DateDiff("yyyy", dtDob, dtTdy)
    lblQtrs.Caption = "Quaters difference is: " & DateDiff("q", dtDob, dtTdy)
    lblWks.Caption = "Weeks difference is: " & DateDiff("ww", dtDob, dtTdy, vbSunday)
    frmDateDiff.Height = 217
        
    Application.ScreenUpdating = True
    
End Sub

Private Sub UserForm_Initialize()
    frmDateDiff.Height = 119
End Sub

Sub Rectangle1_Click()
    frmDateDiff.Show
End Sub

Display UserForm to find difference between two dates on the WorkSheet:

Here are steps to calculate difference between two dates using UserForm on the worksheet.

1. Place any shape by clicking on insert menu from illustrations group.
2. Right click on the shape, select Edit Text. Enter name as ‘Show UseForm’.
3. Format shape according to your wish.
4. Again right click on the shape, select assign macro. Add the following statement.

frmDateDiff.Show

Instructions to Execute the Procedure:

You can download the below file and see the code and execute it. Or else, you create new workbook and use the above code and test it. Here are the instructions to use above code.

1. Open VBA Editor window or Press Alt+F11.
2. Insert a new UserForm and design the userform as per the above mentioned instructions.
3. Double click on the userform. Copy the above procedure and paste it in the code window.
4. You can hit show UserForm from on the Worksheet.
5. Select two dates by clicking on date and time picker control.
6. Click on Calculate button. Now you can see the final output on the UserForm below the Calculate control button.

Final Outcome of the project:

Here is the sample screen shot of the entire project output for your reference.

Download Excel Macro File:

Here is the workbook macro file to explore yourself. This file helps you to calculate difference between two dates.

Excel VBA Difference Between Two Dates