VBA DateValue Function
The VBA Date Value function converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code. It is a built-in VBA function which is also available as worksheet function.
Remember, the important thing we need to understand in Excel is that dates are represented by serial numbers. When the date format is applied to the serial number, we can see the date in the applied format.
For example, look at the following date in Excel.
Now, we can use the VBA Date value function to get the date in another cell.
Also, the following code is for your reference.
Sub Date_Value_Basic()
Range(“B2”).Value = DateValue(Range(“A2”).Value)
End Sub
Now, this will give us the result in cell B2 as shown below.
Next, let us learn the syntax of the DATEVALUE function in VBA.
Remember, there is only one argument in the DATEVALUE function in VBA i.e., a string that represents the valid date as per Visual Basic Language. Henceforth, it is mandatory.
Table of Contents
Key Takeaways
- VBA DateValue function is an in-built function used to convert date that represents Microsoft Excel date-time code. It is available both as worksheet function as well as VBA function.
- The syntax of the DATEVALUE function in VBA is =DATEVALUE(Date As String), where Date as String is the only mandatory argument.
- We can convert the text stored date to correct date by using the DateValue function in VBA.
- DatePart is the VBA function not available as worksheet function.
- DatePart function can extract quarter, week of the year, weekday of the given date.
Explanation
For example, look at the following date in Excel.
Now, we have the date 14th Feb 2023 in cell B2. Basically, this date is derived from the serial number. To see the serial number, change cell B2 format to the whole number.
So, the serial number 44971 represents the date 14-02-2023 when the date format in excel was applied. So basically, 14th Feb 2023 is the date and 44971 is the date value.
First let us demonstrate one example using the worksheet DATE VALUE function in Excel.
For example, look at the following date in Excel cell A2.
Next, to get the value of the date in cell A2, let us apply the DATEVALUE function in cell B2.
Now, when we applied the date value function in cell B2, we have got the #VALUE! in return. The reason for this is that the value in cell A2 is a proper date format and stored as date itself.
Remember, the DATE VALUE function retrieves the date value only from the text stored date cells only. To make it work, lets convert the value in cell A2 to text by entering the single quote (‘) before the start of the date.
After entering the single quote before the date, press the Enter key to convert the value in cell A2 to text value.
As soon as the date is converted to text in cell A2, the Date Value function works perfectly without any error. This is one of the specialties of the date value function in Excel.
So, the Date Value function is used to get the date value from the date which is stored as text in nature through VBA.
Now, let’s move on to the VBA part of date value function.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use Excel VBA DATEVALUE?
Let us have few basic examples of using the VBA DATEVALUE function in Excel before we move on to the advanced practical examples.
Example #1 – Get The Serial Number Of The Date By Using The DATEVALUE Function
The first fundamental to use the DATEVALUE function is to check if the date is stored as text or not. For example, look at the following date in an Excel cell.
Clearly, we can see that the value in cell A2 is stored as text. Just to confirm, let us apply the simple ISNUMBER function in cell B2.
It is confirmed that the value in cell A2 is not a number or date but a text stored date.
To convert this to date, we need to get the date value for this date. Now, let’s follow the steps listed below.
Step 1: First, start sub routine by naming the macro like the following.
Step 2: Next, define a variable to store the data of cell A2 in the form of date.
Step 3: Then, assign the value of cell A2 to this variable.
Step 4: Now, let’s store this variable name in cell A3.
The complete code is here below for you.
Sub Date_Value_Ex1()
Dim String_Date As Date
String_Date = Range(“A2”).Value
Range(“A3”).Value = String_Date
End Sub
Now, execute the code by pressing F5 in the Visual Basic Editor by placing the active cursor inside the above macro, and then, we will get the date in cell A3 like the following.
To see the difference between the first value and the second, lets apply the ISNUMBER excel function in cell B3 as well;
Now, the date in cell A3 is stored as date and not as text. Hence, the ISNUMBER function returned TRUE as the result.
Example #2 – The Importance Of DATEVALUE Function In VBA
For example, look at the following VBA code in Excel.
Part 1: Dim String_Date As String
Here, we have defined the variable “String_Date” and assigned the data type as “String” (Text) not as Date. In the previous case, we have assigned the data type as date.
Part 2: String_Date = “23-05-2023”
Now, we are assigning the date in double quotes for the defined variable.
Part 3: Range(“A10”).Value = String_Date
We are storing the assigned date in cell C5.
Now, let’s execute this code.
We have the date in cell C5. However, the date is not in the right format because it is stored as text because the data type of the variable was “String”.
To confirm this, use the ISNUMBER function for the stored date in cell C5.
Clearly, the ISNUMBER function has returned the output as FALSE i.e., value in cell C5 is not date; rather, it is a text.
To avoid this error, we will use DATEVALUE function. The following code will eliminate the text storing date method.
Sub Date_Value_Ex2()
Dim String_Date As String
String_Date = “23-05-2023”
Range(“C5”).Value = DateValue(String_Date)
End Sub
The only change we made from previous code is that, we have surrounded the variable “String_Date” with DATEVALUE function, while we are storing the variable in cell B5.
Now, look at the result in cell C5.
Now, the value stored in cell C5 is date because ISNUMBER function has returned the result as TRUE.
Examples
Now, let’s explore the advanced usage of DATE VALUE function in VBA.
Example #1 – Obtain Day, Month, And Year From A Date
Any date comprises of 3 parts i.e., Day, Month, and Year. Often times, we may need to get the different part of the date separately. In this case, we can use the DATEVALUE function to extract the day, month, and year separately.
For example, look at the following data in Excel.
Now, we have many dates, so, we need to include the FOR loop to get day, month, and year.
The following code will obtain the parts of the date in each separate column.
Sub Date_Value_Obtain_Dates()
‘To loop through 3 times.
Dim k As Long
‘To loop through dates and get day of the date.
Dim D As Long
‘To loop through dates and get month of the date.
Dim M As Long
‘To loop through dates and get year of the date.
Dim Y As Long
‘To find the last used row of the worksheet
Dim LR As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Loop through all the dates and extract day from the date
For D = 2 To LR
Cells(D, 2).Value = Day(DateValue(Cells(D, 1).Value))
Next D
‘Loop through all the dates and extract month from the date
For M = 2 To LR
Cells(M, 3).Value = Month(DateValue(Cells(M, 1).Value))
Next M
‘Loop through all the dates and extract year from the date
For Y = 2 To LR
Cells(Y, 4).Value = Year(DateValue(Cells(Y, 1).Value))
Next Y
End Sub
Next, copy the above code to Visual Basic Editor window.
Now, run the code and we will get the following result in the worksheet.
Example #2 – Using The DatePart To Get The Different Parts Of Date
Not only the DATEVALUE function allows us to extract various parts of the date, even we can use the DATEPART function in VBA to get the different parts of the date.
Let’s look at the syntax of the DATEPART function first.
DatePart(Interval As String, Date, [First Day of Week], [First Week of Year])
Interval: A string expression tells us what part of the date we need. We can take the reference from the following table and use it accordingly.
Setting | Description |
---|---|
yyyy | Year |
q | Quarter |
m | Month |
y | Day of year |
d | Day |
w | Weekday |
ww | Week |
h | Hour |
n | Minute |
s | Second |
Date: The date from which we are trying to extract the given interval (argument 1).
These are the 2 mandatory arguments; we can ignore the other 2 arguments at this point of time.
Example
Let’s work out the same data that we have used the previous example here as well but in a different worksheet.
The specialty of the DATEPART function is that we can extract quarter, week, along with the day, month, and year.
Also, we can use the following code to extract all the required parts of the date.
Sub DatePart_Obtain_Dates()
‘To loop through 3 times.
Dim k As Long
‘To loop through dates and get day of the date.
Dim D As Long
‘To loop through dates and get month of the date.
Dim M As Long
‘To loop through dates and get year of the date.
Dim Y As Long
‘To find the last used row of the worksheet
Dim LR As Long
‘To loop throuh dates and get quarter
Dim Q As Long
‘To loop throuh dates and get week
Dim W As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Loop through all the dates and extract day from the date
For D = 2 To LR
Cells(D, 2).Value = DatePart(“d”, Cells(D, 1).Value)
Next D
‘Loop through all the dates and extract month from the date
For M = 2 To LR
Cells(M, 3).Value = DatePart(“m”, Cells(M, 1).Value)
Next M
‘Loop through all the dates and extract year from the date
For Y = 2 To LR
Cells(Y, 4).Value = DatePart(“yyyy”, Cells(Y, 1).Value)
Next Y
‘Loop through all the dates and extract quarter from the date
For Q = 2 To LR
Cells(Q, 5).Value = DatePart(“q”, Cells(Q, 1).Value)
Next Q
‘Loop through all the dates and extract weekday from the date
For W = 2 To LR
Cells(W, 6).Value = DatePart(“ww”, Cells(W, 1).Value)
Next W
End Sub
When we run the above code, we will get the following parts of the date.
Important Things To Note
- VBA DateValue function would return Runtime Error 13 when the data type mismatch. This means the given value cannot be converted to date by the date value function.
- In worksheet, date value function works only when the date stored as a text, otherwise we will get #VALUE! Error.
- DatePart requires correct interval code in double quotes, otherwise we will get an error.
- By default, the DatePart function takes Sunday as the week start day.
Frequently Asked Questions (FAQs)
The basic difference between the DateValue and CDate is that DateValue gives us only the date meanwhile the CDate function gives us the date along with the time as well.
For example, look at the following code in VBA.
Sub DateValue_vs_CDate()
Dim DV As Date
Dim CD As Date
DV = DateValue(“2023-08-24 08:05:15”)
CD = CDate(“2023-08-24 08:05:15”)
MsgBox DV
MsgBox CD
End Sub
In the above code, we have defined 2 variables i.e., one to convert the date using the DateValue function and other one using the CDate function.
Now, the date that we have provided is in the combination of date and time.
Next, let’s execute the code and see what is the difference between DateValue and CDate functions.
Remember, when we use the DateValue function, we got only the date part even though we have provided the date and time.
Next, click on OK and we get the result of the CDate function.
CDate function gives us both date and time.
DateValue function would return Runtime Error 13 when the data type is mismatched. This means the given value cannot be converted to date by the date value function.
By using the VBA Date Value function, we can convert the text stored dates into actual date format thus allowing us to work smoothly with the date data.
Download Template
This article must help understand the VBA DateValue, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA DateValue. Here we explain how to use DateValue function in Excel VBA with examples and downloadable template. You may learn more from the following articles –
Leave a Reply