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.
Example 1 – Using VBA DateValue Function to Convert String/Text into Date
- Open the Developer tab >> from Insert >> select Button from Form Controls.
- Drag the Button to place it where you want to give the caption.
A dialog box of Assign Macro will open.
- Enter the Macro name and select Macros in.
We entered the Macro name as Show_Data Value and selected VBA DateValue.xlsm from Macros in.
- Click New.
It will redirect to the module of Microsoft Visual Basic for Applications.
- Enter 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
In the Sub Show_DateValue(), we declared the SampleDate variable as Date and used it to keep the returned value of the DateValue function.
We used the MsgBox to show the date string as Date.
- Save the code and go back to the worksheet and click on the Button.
Result
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
- Open the Developer tab >> select Visual Basic.
A new window Microsoft Visual Basic for Applications will open.
- From Insert >> select Module.
- Enter the following code in the Module.
Sub Cell_Reference_DateValue()
Dim StringDate As Date
StringDate = DateValue(Range("C8").Value)
MsgBox StringDate
End Sub
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.
We used the Range and Value method to reference the cell value from the sheet.
We used the MsgBox to show the date string as Date.
- Save the code and go back to the worksheet.
- Open the Developer tab >> from Insert >> select Button from Form Controls.
- Drag the Button to place it where you want to give the caption
A dialog box of Assign Macro will open.
- Select the Macro name and Macros in.
We selected the Macro name Cell_Reference_DateValue and selected VBA DateValue.xlsm from Macros in.
- Click OK.
- Click on the button named Cell Reference DateValue.
Result
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. In the sample dataset, the date is in Text format.
- Open the Visual Basic for Applications window as explained in the previous section.
- Enter the following code in the Module.
Sub Show_Date()
Dim SampleDate As Date
SampleDate = DateValue("6/1/2020")
MsgBox SampleDate
End Sub
In the Sub Show_Date(), we declared the variable SampleDate as Date and used it to keep the returned value of the DateValue function.
We used the MsgBox to show the date string as Date.
- Save the code and go back to the worksheet and click on the Button.
We named the button Date.
You can follow the steps explained in the previous section to name the inserted button.
Result
It will display the message box with the date.
Similar Readings
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.
The date of the C7 cell is in Text format.
- Open Visual Basic for Applications.
- Enter 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
In the Sub Show_Date_Month_Year(), we declared the variable StringDate as Date and used it to keep the returned value of the DateValue function.
We used the Year and Month function to show the year and month of the input date.
We used three MsgBox to show the date string as Date, Year, and Month.
- Save the code and go back to the worksheet and click on the Button.
We named the button Date_Month_Year.
You can name the inserted button by following the steps explained in the previous section.
Result
It will display the message box with the date, year and month in sequence.
It will show the Date.
Click OK and it will show the Year.
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.
We’ll use the date of cell C9 which is in Text format.
- Open VBA.
- Enter 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
In the Sub Show_DatesPart(), we declared the variable SampleDate as Date and used it to keep the returned value of the DateValue function.
We used the DatePart function to display year, date, month, and a quarter, and applied the respective format “yyyy”, “d”, “m”, and “q”.
We used five MsgBox to show the date string as Date, Year, Month, single Date and Quarter.
- Save the code and go back to the worksheet and click on the Button.
We named the button Date & DatePart.
Follow the steps explained in the previous section to name the inserted button.
Result
It will display the message box with the date, year, month, single date and quarter one by one.
It will show the whole Date.
Click OK to get the Year.
Click OK to get the Month.
Click OK to get the Day which is the single Date.
To see the Quarter, click OK.
Example 6 – Using VBA DateValue Function to Place Converted Date in a Cell
We will use the date of cell C4 to convert it into Date from Text and place the converted date into the D4 cell.
- Open VBA.
- Enter the following code in the Module.
Sub String_To_Date_in_Sheet()
Range("D4").Value = DateValue(Range("C4"))
End Sub
In the Sub String_To_Date_in_Sheet(),
We used the cell reference as input in the DateValue function. We used the Range and Value method also provided the cell reference where we want to keep the returned date.
- Save the code and go back to the worksheet and click on the Button.
We named the button String to Date in Sheet.
Follow the steps explained in the previous section to name the inserted button.
Result
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, if you provide any date which is not a string date and if it is a text value then the DateValue function won’t work and will show 13 number error which is a mismatched type.
Let’s take a sample date “7/1/2019”.
- Open VBA.
- Enter the following code in the Module.
Sub Convert_Date()
Dim SampleDate As Date
SampleDate = DateValue(Range("C8"))
MsgBox SampleDate
End Sub
In the Sub Convert_Date(), we declared the variable SampleDate as Date and used it to keep the returned value of the DateValue function.
We used the Range and Value method to refer to the cell value from the sheet.
We used the MsgBox to show the date string as Date.
- Save the code and go back to the worksheet.
- Select the cell to run the macro.
- Open the View tab >> from Macros >> select View Macro.
A dialog box of Macro will open.
- Select the Macro name and select Macros in.
We selected the Macro name as Convert_Data and selected VBA DateValue.xlsm from Macros in.
- Click Run.
You will get an error message of mismatch.
If you use any date without string representation in the VBA DateValue function, it will also show an error.
- Run the following code.
It will show Run-time error ‘13’: Type mismatch.
Things to Remember
If you don’t provide a date in proper text format, 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.
Download Practice Workbook
Further Readings
- How to Use Excel VBA MONTH Function (7 Suitable Examples)
- Now and Format Functions in Excel VBA (4 Examples)
- How to Use VBA DateSerial Function in Excel (5 Easy Applications)
- Date Variable in VBA Codes (7 Uses of Macros with Examples)
