VBA DATEVALUE

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

In this article, we will learn examples of the VBA DateValue function and how to use it with a clear explanation.

In VBA, the DateValue function uses the following syntax.

This function uses only a single argument or parameter:

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

The VBA DateValue function can interpret data represented in text format mentioned in a valid Excel format. However, it cannot return the date value if the string includes the text representation of weekdays.

The benefits of the VBA DateValue function are extracting date value from string and converting date with time to the only date. So, we can simplify that when we give a date with time, this function only gives the date value avoiding the time value.

First, open the VBA editor to use the DateValue function in Excel.

The command button needs to be placed in the Excel worksheet to add the lines of the VBA program. To implement the program lines, the user must click on the Excel sheet's "Command Button." Valid input is given through the argument to have a valid output from the program. 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.

Below are the examples of DateValue in Excel VBA.

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

We must follow a few steps to create and execute the program in VBA. Those include:

Step 1: Go to the "Developer" tab.

• Place the cursor on a cell in the Excel sheet.
• Click on the "Insert" option.
• 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."

One should develop the code 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 dates are variable with Date data type and Msgbox to display the output.

Step 3: While developing code, VBA type mismatch errors will occur and must be taken care of.

Step 4: Run the program by 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 we execute the program, it first displays the message box with the date given in the text input. Next, click on "OK" to see the year value again, and click "OK" on the message box to see the month value.

Note: One must follow these steps clearly to have accurate results.

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

Step 1: Go to the Excel Developer Tab, place the cursor on a cell in the Excel sheet, 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 "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, one must apply the format as "yyyy," "d," "m," and "q." If we make any mistake in the format, it displays the following error.

Step 4: After successfully debugging the program, run the program by clicking on the "Run" button to 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, DateValue, Month Value, Quater Value, respectively.

One must remember the following things while using the DateValue function in Excel VBA:

• Run time error 13 with the message "Type Mismatch" is displayed when the date provided to the DateValue function cannot convert into a valid date. We need the date in 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 selecting the macro created for the respective program.
• When DatePart is used to get the values, we should follow the appropriate format. Otherwise, it leads to 'run time error 5' with a message invalid procedure call or argument.