VBA DATEVALUE

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 in VBA. This function returns the serial number or value of Date provided in the string representation format ignoring the information of time supplied by the 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 a 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 editorOpen The VBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more.

The command button needs to be placed in the Excel worksheet to add the lines of the VBA program to it. To implement the lines of the program, the user requires to click on the command button in the excel sheet. To have a valid output from the program, valid input is given through the argument. For example, the following code helps 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.

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 the cursor on a cell in the Excel sheet and click on the ‘Insert’ option and choose ‘Command Button’ under ActiveX Control, as shown in the figure.

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 the VBA project and write the code between the Private Sub-command button and the 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 example date are variable with Date data type and Msgbox to display the output.

Step 3: While developing code, VBA 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 the 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.

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

Step 1: Go to Excel Developer TabExcel Developer TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more, place the cursor on a cell in the Excel sheet and click on the ‘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 the 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 make any mistake in the format, it displays the following error.

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

The code first displays the full date, and 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 a 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.

Recommended Articles

This has been a guide to VBA DateValue. Here we will learn how to use VBA DateValue function to returns the serial number or value of Date provided in the string along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Save As
• VBA DatePart
• Use DateDiff Function in VBA
• VBA DateAdd