VBA DateValue

VBA DateValue Function

While working in VBA we work with dates. We assign dates to various variables or we can use the DATEVALUE function to do so. DATEVALUE is both worksheet and Visual Basic function. In worksheet, it gives a serial date. When we use this function in VBA it gives us a date value. As I told above the DATEVALUE function used in VBA returns a date value. The data type we used for our variables while using this function is date. It is somewhat similar to the worksheet function. How we use this function is pretty simple, it takes a date as an argument.

Syntax of DateValue in Excel VBA

The syntax for the VBA DateValue Function in excel is as follows:

What this function does that it takes a value or an argument as a string and returns it to a date. Why we use this function is because we work with lots of data and every human being is not similar so there will be a difference in entering date formats for each and every different person. If we have to do some calculations over the dates on the data then we will have to convert every different format of the dates available in the data onto one single format and then perform the calculation. Now imagine the large chunk of data with a large chunk of different types of dates.

Let us look by a few examples of how we can use this DATEVALUE function in VBA through some examples which will give us some more clear idea on how this function works.

How to Use VBA DateValue in Excel?

We will learn how to use a VBA DateValue Function with few examples in excel.

Example #1 – VBA DateValue

Let us begin with a very simple step. We will assign a variable a date value as a string and then print the date value for that variable.

Step 1: Now once we are inside the editor we will have a lot of options in the header, from the insert tab insert a new module in the editor where we will perform all the example demonstrations,

Step 2: Now let us start our procedure as shown below.

Code:

Sub Sample()

End Sub

Step 3: Let us declare a variable as Date data type as shown below,

Code:

Sub Sample()

Dim A As Date

End Sub

Step 4: Now let us provide variable A with some value using the DATEVALUE function as shown below.

Code:

Sub Sample()

Dim A As Date
A = DateValue("02/02/2019")

End Sub

Step 5: I have provided the date in double inverted commas to treat it as a string, now use the msgbox function to display the value stored in A.

Code:

Sub Sample()

Dim A As Date
A = DateValue("02/02/2019")
MsgBox A

End Sub

Step 6: Execute the above code and see the result.

We know that in VBA date is provided in # symbols and a string is provided in “ double quotes. But DATEVALUE changes the value to date.

Example #2 – VBA DateValue

Now let us take an input from a user in date because a user can provide a date in any format. We will convert the value given by the user in the date using the DATEVALUE function.

Step 1: From the Insert, select a new module in the editor.

Step 2: Now let us start another subprocedure as follows.

Code:

Sub Sample1()

End Sub

Step 3: Declare two variables as dates as we will take value from input from user in one variable while in another we will use the DATEVALUE function to convert it to a date.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date

End Sub

Step 4: In the input data variable take the input from the user using the input box function.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date
InputDate = InputBox("Enter a Date")

End Sub

Step 5: In the Outputdate variable let us change the date we get as input to date using the DATEVALUE function as shown below.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date
InputDate = InputBox("Enter a Date")
OutputDate = DateValue(InputDate)

End Sub

Step 6: Now let us display the value we have now using the msgbox function.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date
InputDate = InputBox("Enter a Date")
OutputDate = DateValue(InputDate)
MsgBox OutputDate

End Sub

Step 7: Run the above code and give an input date as follows.

Step 8: Click on OK and see the final result as follows.

Although the date format from the user was different still we have successfully converted it into a date.

Example #3 – VBA DateValue

We can see dates in our sheet and how to use DATEVALUE function on them? I have a date in cell A1 as follows.

Let us change it to the standard date we have.

Step 1: Again in the same module start another subprocedure as shown below.

Code:

Sub Sample2()

End Sub

Step 2: Declare a variable as Date as shown below.

Code:

Sub Sample2()

Dim Dt As Date

End Sub

Step 3: Now in the Dt variable, store the value of the cell A1 which is as follows using the DATEVALUE function.

Code:

Sub Sample2()

Dim Dt As Date
Dt = DateValue(Sheet1.Range("A1").Value)

End Sub

Step 4: Now let us display the value stored in our variable.

Code:

Sub Sample2()

Dim Dt As Date
Dt = DateValue(Sheet1.Range("A1").Value)
MsgBox Dt

End Sub

Step 5: Now execute the above code and let us see the result.

Things to Remember

• DATEVALUE is a date function in VBA.
• It is somewhat similar to the worksheet function, worksheet function gives us the serial numbers of the date while in VBA it gives us the DATEVALUE.
• This function takes the argument as the date in the string and converts it in the string.

Recommended Articles

This is a guide to VBA DateValue. Here we discuss how to use Excel VBA DateValue Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Copy Paste Function in VBA
2. Excel VBA Date Format
3. VBA Subscript out of Range
4. Excel ISNUMBER Formula