Excel VBA UserForm: Difference Between Two Dates

We are going to look at calculate difference between two dates using Excel VBA UserForm. In this section, we are calculating difference between two dates in days, weeks, months, weeks and years. Following is the step by step detailed explanation to automate this project using 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:

Control Property Value
UserForm Name frmDateDiff
Caption Difference Between Two Dates
Label Name lblDOB
Caption Enter First Date:
Label Name lblCurDate
Caption Enter Second Date:
DTPicker Name DTPkr1
CustomFormat ddMMMyyyy
Format 3 – dtpCustom
DTPicker Name DTPkr2
CustomFormat ddMMMyyyy
Format 3 – dtpCustom
CommandButton Name cmdCal
Caption Calculate
Label Name lblYrs
Caption Years
Label Name lblMnts
Caption Months
Label Name lblDays
Caption Days
Label Name lblQtrs
Caption Quarters
Label Name lblWks
Caption Weeks

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

difference-between-two-dates_op

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

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

Share This Story, Choose Your Platform!