In Excel, various people use different date formats so it is required to have any function to convert any formats into a standard date format. Whenever you want to convert a date string into a date then you can use the in-built Excel VBA DateValue function. There is also a DATEVALUE function for the Excel worksheet which returns the serial date. VBA DateValue function returns the serial number or value for the provided Date in the string representation format.
In this article, I will show you various examples of using the Excel VBA DateValue function.
Basics of VBA DateValue Function: Summary & Syntax
Summary
The Excel VBA DateValue function takes a value or an argument in string representation and returns it as a date value. This function is helpful to convert different types of data into one single format.
Syntax
DateValue(Date As String)
Arguments
| Arguments | Type | Required/Optional | Explanation |
|---|---|---|---|
| Date | String | Required | It is the date that is represented in the string format. |
Return Value
The VBA DateValue function returns a date value.
Version
The VBA DateValue function is available for Excel 2003 and later.
I’m using Excel Microsoft 365 to implement these examples.
Example-1: Using VBA DateValue Function to Convert String/Text into Date
If you want to convert a date string into a date, then you can use the DateValue function of VBA.
Let me explain the procedure to you,
To convert a date string into a date and to show,
First, open the Developer tab >> from Insert >> select Button from Form Controls
Now, Drag the Button to place it where you want to give the caption
⏩ A dialog box of Assign Macro will appear.
Then, provide the Macro name and select Macros in.
⏩ I provided the Macro name as Show_Data Value and selected VBA DateValue.xlsm from Macros in.
Then, click New.
➤ Now, it will redirect to the module of Microsoft Visual Basic for Applications.
Now, type the following code between the Sub and End
The code is:
Sub Show_DateValue()
Dim SampleDate As Date
SampleDate = DateValue("January 5,2022")
MsgBox SampleDate
End Sub
Here, in the Sub Show_DateValue(), I declared the SampleDate variable as Date and used it to keep the returned value of the DateValue function.
Then used the MsgBox to show the date string as Date.
Then, Save the code and go back to the worksheet and click on the Button.
Result
Now, it will display the message box with the date used in the text input of the DateValue function.
Read More: VBA Date to String Conversion in Excel
Example-2: Applying VBA DateValue Function with Cell Reference
If you want to reference the date from your sheet instead of providing it in the DateValue function directly you also can do it.
To begin with,
Open the Developer tab >> select Visual Basic
➤ Now, a new window Microsoft Visual Basic for Applications will appear.
Then, from Insert >> select Module
Now, type the following code in the Module.
Sub Cell_Reference_DateValue()
Dim StringDate As Date
StringDate = DateValue(Range("C8").Value)
MsgBox StringDate
End Sub
Here, in the Sub Cell_Reference_DateValue(), I declared the variable StringDate as Date and used it to keep the returned value of the DateValue function.
I also used the Range and Value method to reference the cell value from the sheet.
Then used the MsgBox to show the date string as Date.
Now, Save the code and go back to the worksheet.
Again, open the Developer tab >> from Insert >> select Button from Form Controls
Now, Drag the Button to place it where you want to give the caption
⏩ A dialog box of Assign Macro will appear.
Then, select the Macro name and Macros in.
⏩ I selected the Macro name Cell_Reference_DateValue and selected VBA DateValue.xlsm from Macros in.
Finally, click OK.
Then, click on the button named Cell Reference DateValue.
Result
Now, it will display the message box with the date.
Example-3: Converting String/Text into Date Using VBA DateValue Function in Excel
You can convert different types of string format date into a date. Let me show you another format.
Here, the date is in Text format.
Now, to open the Visual Basic for Applications window you can follow the steps explained in the previous section
Then, type the following code in the Module.
Sub Show_Date()
Dim SampleDate As Date
SampleDate = DateValue("6/1/2020")
MsgBox SampleDate
End Sub
Here, in the Sub Show_Date(), I declared the variable SampleDate as Date and used it to keep the returned value of the DateValue function.
Then used the MsgBox to show the date string as Date.
Now, Save the code and go back to the worksheet and click on the Button.
➤ I named the button Date.
You can follow the steps explained in the previous section to name the inserted button.
Result
Hence, it will display the message box with the date.
Similar Readings
- Use the VBA DateAdd Function in Excel
- How to Sort Dates in Excel by Year (4 Easy Ways)
- Use Excel Date Shortcut
- How to Use the Day Function in Excel VBA (3 Examples)
Example-4: Changing String/Text Date into Date, Month & Year
You can convert the date string into date along with month and year by using the VBA Year function and the Month function along with the DateValue function.
Here, the date of the C7 cell is in Text format. I will show you the date, year and month of this date.
Now, to open the Visual Basic for Applications window you can follow the steps explained in the previous section.
Then, type the following code in the Module.
Sub Show_Date_Month_Year()
Dim StringDate As Date
StringDate = DateValue("6/1/2020")
MsgBox StringDate
MsgBox Year(StringDate)
MsgBox Month(StringDate)
End Sub
Here, in the Sub Show_Date_Month_Year(), I declared the variable StringDate as Date and used it to keep the returned value of the DateValue function.
I also used the Year and Month function to show the year and month of the input date.
Then used three MsgBox to show the date string as Date, Year, and Month.
Now, Save the code and go back to the worksheet and click on the Button.
➤ I named the button Date_Month_Year.
Here, you can name the inserted button by following the steps explained in the previous section.
Result
Therefore, it will display the message box with the date, year, and month one by one.
First, it will show the Date.
Now, click OK and it will show the Year.
Again, click OK and it will show the Month.
Read more: How to Use Year Function in Excel VBA
Example-5: Implementing VBA DateValue & DatePart Functions in Excel
You also can use the VBA DateValue function and the DatePart function to show the date in part of the year, month, day, and quarter.
Here, I will use the date of the C9 cell which is in Text format.
Now, to open the Visual Basic for Applications window you can follow the steps explained in the previous section.
Then, type the following code in the Module.
Sub Show_DatesPart()
Dim SampleDate As Date
SampleDate = DateValue("10/6/2016")
MsgBox SampleDate
MsgBox DatePart("yyyy", SampleDate)
MsgBox DatePart("m", SampleDate)
MsgBox DatePart("d", SampleDate)
MsgBox DatePart("q", SampleDate)
End Sub
Here, in the Sub Show_DatesPart(), I declared the variable SampleDate as Date and used it to keep the returned value of the DateValue function.
I also used the DatePart function to display year, date, month, and a quarter, and applied the respective format “yyyy”, “d”, “m”, and “q”.
Then used five MsgBox to show the date string as Date, Year, Month, single Date, and Quarter.
Now, Save the code and go back to the worksheet and click on the Button.
➤ I named the button Date & DatePart.
Follow the steps explained in the previous section to name the inserted button.
Result
As a result, it will display the message box with the date, year, month, single date, and quarter one by one.
First, it will show the whole Date.
Now, click OK and you will get the Year.
Again, click OK and you will get the Month.
Again, click OK and you will get the Day which is the single Date.
To see the Quarter again click OK.
Read more: How to Use the VBA DatePart Function in Excel
Example-6: Using VBA DateValue Function to Place Converted Date in a Cell
The Excel VBA DateValue function not only shows the date it also keeps the converted string date into any cell of the worksheet.
Here, I’m going to use the date of the C4 cell to convert it into Date from Text and I will place the converted date into the D4 cell.
Now, to open the Visual Basic for Applications window you can follow the steps explained in the previous section.
Then, type the following code in the Module.
Sub String_To_Date_in_Sheet()
Range("D4").Value = DateValue(Range("C4"))
End Sub
Here, in the Sub String_To_Date_in_Sheet(),
I used the cell reference as input in the DateValue function. Then used the Range and Value method also provided the cell reference where I want to keep the returned date.
Now, Save the code and go back to the worksheet and click on the Button.
➤ I named the button String to Date in Sheet.
Follow the steps explained in the previous section to name the inserted button.
Result
Therefore, you will get formatted string date into date format in the D4 cell.
Reasons to Show Error
Although the VBA DateValue function converts all types of dates into date format but if you provide any date which is not a string date rather if it is a text value then the DateValue function won’t work and will show 13 number error which is a mismatched type.
I’m taking a sample date “7/1/2019”
Now, to open the Visual Basic for Applications window you can follow the steps explained in the previous section.
Then, type the following code in the Module.
Sub Convert_Date()
Dim SampleDate As Date
SampleDate = DateValue(Range("C8"))
MsgBox SampleDate
End Sub
Here, in the Sub Convert_Date(), I declared the variable SampleDate as Date and used it to keep the returned value of the DateValue function.
I also used the Range and Value method to refer to the cell value from the sheet.
Then used the MsgBox to show the date string as Date.
Now, Save the code and go back to the worksheet.
After that, select the cell to run the macro.
Then open the View tab >> from Macros >> select View Macro
⏩ A dialog box of Macro will appear.
Then, select the Macro name and select Macros in.
⏩ I selected the Macro name as Convert_Data and selected VBA DateValue.xlsm from Macros in.
Finally, click Run.
Here, you will get an error message of mismatch.
If you use any date without string representation in the VBA DateValue function then it will also show an error.
Run the following code
Then, it will show Run-time error ‘13’: Type mismatch.
Things to Remember
🔺 If you don’t provide a date in proper text format then it will show run time error 13 with message Type Mismatch.
🔺 While using the DatePart remember to use the appropriate format otherwise it will show run time error 5 with message invalid procedure call or argument.
🔺 If you use Button then you can see the output without opening the VBA editor.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained examples.
Download Practice Workbook
Conclusion
In this article, I have shown 6 examples of the Excel VBA DateValue function. I also tried to cover the reasons for showing errors frequently. Along with things you need to remember while using the function. Feel free to comment down below for any types of queries and suggestions.
