VBA DateValue | How to Use DateValue Function in Excel VBA?

What is VBA DateValue Function?

A DateValue function is an in-built function in Excel VBA under the category of Date/time function. It works as both VBA function and worksheet function. This function returns the serial number or value of Date provided in the string representation format ignoring the information of time supplied by Date string. It is used in two different ways in Excel. This function is used as a worksheet formula entering in a worksheet cell. It is used as the macro code in VBA application entering it through Visual Basic Editor associated with Microsoft Excel.

In this article, we are going to learn examples of VBA DATEVALUE and how to use it with clear explanation.

Explanation of VBA Datevalue Function

In VBA, DATEVALUE uses the following syntax.

This function uses only the single Argument or Parameter

Date: It is the date represented in the string format
Returns: This function returns the value of the date when it is used as a VBA function. It returns the date value when it is used as a worksheet function

The VBA DateValue function is able to the interpretation of data represented in text format that is mentioned in a valid Excel format. It is not able to return the date value if the string included the text representation of weekday.

The benefits of the VBA DateValue function are extracting date value from string and converting date with time to the only date. We can simply that when a date is given with time, this function only date value avoiding the time value.

How to Use Excel VBA DATEVALUE?

To use the DateValue function in Excel first needs to open the VBA editor.

Command button needs to be placed in the Excel worksheet to add the lines of VBA program to it. To implement the lines of the program, the user requires to click on the command button in excel sheet. To have a valid output from the program, valid input is given through the argument. For example, the following code helps to the creation of a macro to run the DateValue function to extract the date value from the text in VBA.

VBA program:

Sub Datebutton ()

Dim myDate As Date

myDate = DateValue (“ August 15, 1991”)

MsgBox Date (myDate)

End Sub

This code results in the date as 15 from the given input.

Examples of Excel VBA DATEVALUE

Below are the examples of DateValue in Excel VBA.

You can download this VBA DateValue Excel Template here – VBA DateValue Excel Template

Example #1 – Obtain Day, Month, and Year from a Date

Few steps are followed to create and execute the program in VBA. Those include

Step 1: Go to Developer Tab, place cursor on a cell in the Excel sheet and click on ‘Insert’ option and choose ‘Command Button’ under ActiveX Control as shown in the figure.

Popular Course in this category

VBA Training (3 Courses, 12+ Projects)4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Related Courses

Drag the Button where you want and give the caption as Datebutton from the properties window.

Step 2: Double Click on the button it will redirect to VBA project and write the code between the Private Sub command button and end sub.

The code should be developed as below to get a date, month, and year.

Code:

Private Sub Datebutton1_Click()

  Dim Exampledate As Date

  Exampledate = DateValue("April 19,2019")
  MsgBox Date
  MsgBox Year(Exampledate)
  MsgBox Month(Exampledate)

End Sub

In this code, Datebutton1_Click() is the name and exampledate are variable with Date data type and Msgbox to display the output.

Step 3: While developing code, type mismatch errors will be occurred and needs to take care of them.

Step 4: In this step, run the program clicking on the run option.

Or we can check or debug the program step by step selecting Step Into option under the Debug menu. If our code is without any errors it displays the output.

Step 5: When the program is executed, it first displays the message box with the date given in the text input. Next, click on OK to see the year value again click OK on the message box to see the month value.

Note: These steps are to be followed clearly to have accurate results.

Example #2 – Using the DatePart to Get the Different Parts of Date

Step 1: Go to Developer Tab, place cursor on a cell in the Excel sheet and click on ‘Insert’ option and choose ‘Command Button’ under ActiveX Control as shown in the figure.

Step 2: Drag the button and give the caption as DatePart under properties.

Double click on this button, it directs to the Visual Basic Editor sheet and displays as follows.

Step 3: Develop the code using the DatePart with DateValue as shown in the figure.

Code:

Private Sub Datepart1_Click()

 Dim partdate As Variant
 partdate = DateValue("8/15/1991")
 MsgBox partdate
 MsgBox Datepart("yyyy", partdate)
 MsgBox Datepart("dd", partdate)
 MsgBox Datepart("mm", partdate)
 MsgBox Datepart("q", partdate)

End Sub

In this program, DatePart1 is the macro name and partDate is argument name with data type ‘variant’. For displaying year, date, month, and a quarter, the format is applied as “yyyy”, “d”, “m”, and “q”. If we do any mistake in the format it displays the following error.

Step 4: After successful debugging of program, run the program by clicking on the run button use excel shortcut key F5.

The code first displays the full date then after clicking every OK from the msgbox, it shows the year value after that Date value, Month Value, Quater Value respectively.

Things to Remember About the VBA DATEVALUE

The following things must be remembered while using the DateValue function in Excel VBA

Run time error 13 with message Type Mismatch is displayed when the date provided to the DateValue function is not able to convert into a valid date. We need date is proper text format
When we try to get the only date from the string argument with code ‘msgbox date (argument name)’, it displays the type mismatch error.
We can see the output of the DateValue function without opening the VBA editor. It is done by clicking the command button and select the macro created for the respective program
When DatePart is used to get the values, the appropriate format should be followed. Otherwise, it leads ‘run time error 5’ with message invalid procedure call or argument.