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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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


Related pages


hide column excelms excel 2010 developer tabexcel vba end subhow to protect a worksheet in excelhow to hyperlink in excelexcel 2010 hide rowsunlock protected excel workbookexcel concanatehow to find out duplicates in excelexcel form macroarray lookup excelexcel 2010 pivot table calculated fieldpassword protect excel 2010dml ddl dclexcel macro printexcel duplicate linesexcel iferrorcalculator codingformula of hlookuphow to delete lines in excelproject tracker template excelhow to create a project timeline in excelvlookup function exampleshow to delete an excel sheetexcel vba copy worksheetfree resource planner excel templateexcel copy worksheet vbaexcel function substituteaccess vba function return valueddl dml and dclexcel formula for finding duplicatesrept functionvba columns selectexcel vba breaktick on exceldelete columns vbaexcel tick boxesexcel delete hidden rowsexcel vba option explicitexcel vba array of stringsexample of vba codedata analyst interview questions and answers for freshersvba do while loop exampleexcel vba open files in folderactive range vbadashboard report excelvba selection.deletevba program exampleshow to merge to cells in excelvba excel substitutehow to unlock excel protected sheetuse vlookup in vbafresher hr interview questions and answers pdfexcel macro passwordproject management checklist template exceldelete duplicates excel 2010countifs function in excel 2007arrange ascending order in excelvba msgbox buttonsexcel shortcuts and formulashyperlink vba excelworkbook and worksheetmacro to delete duplicate rows in excelaverageif exampleif cell contains text then excelvba form loadhow to enable macros in excel sheethow to password protect excel file 2010drop down lists in excel 2007testing interview questions and answers for freshersunprotect an excel worksheetvba macro tutorial pdfdynamic excel chartascending in excelinterview aptitude questions and answers with explanationtick excelvba code to export access query to excellearn macroseliminate duplicate rows in exceloption button excel vba