How to automate date validation using VBA in Excel.
As the video below shows, date variables display dates according to the system’s short date format, and they display times according to your system’s time format (either 12- or 24-hour formatting). We can modify the date settings through the Time and Language Options in Windows 10 dialog box in the Windows 10 Settings Panel. Therefore, the displayed date or time format will vary, depending on the settings for the system on which the application is running.
When we write VBA code, however, we must use the U.S. date formats (such as mm/dd/yyyy). So the following statement assigns a day in October to the OurDate variable (even if our system is set to use dd/ mm/yyyy for dates): OurDate = #10/11/2013#
When we display the variable with the MsgBox function, VBA shows OurDate using our system settings. So if your system uses the dd/mm/ yyyy date format, OurDate will be displayed as 11/10/2013.
It’s important to note the following when working with dates in Excel:
- Date variables display dates based on our system’s short date format
- Time is displayed according to our system’s format
- We can modify our date and time settings via ‘Time & Language’ options in Windows 10 Settings
- We always use the US date formats (mm/dd/yyyy) when we write the VBA code. Therefore, OurDate=#11/12/2019# assigns this value to 12th November 2019 and not 11th December 2019 even if our system is set to display dd/mm/yyyy
Watch the video below:
Here’s the complete VBA code for dates validation:
Dim myDate As Date
myDate = Range(“A2”)
MsgBox “The date is ” & myDate
myDate = #11/10/2018#
Range(“B2”) = myDate
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rng As Range
Set ws = Sheets(“sheet1”)
Set rng = ws.Range(“A2:A100”)
For Each c In rng
If c.Value <> “” And Not IsDate(c) Then
MsgBox “Only dates are allowed here!”