Excel VBA UserForm: Difference Between Two Dates

Home/VBA Code Explorer/Projects/Excel VBA UserForm: Difference Between Two Dates

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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

LIMITED TIME OFFER

Leave A Comment