VBA DateValue

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.

VBA Date Value Intro

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.

VBA Date Value Intro - Output

Next, let us learn the syntax of the DATEVALUE function in VBA.

VBA Date Value - Syntax

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.

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.

VBA Date Value - Use - Example

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.

VBA Date Value - Use - Example - Number format

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.

VBA Date Value Intro

Next, to get the value of the date in cell A2, let us apply the DATEVALUE function in cell B2.

VBA Date Value - Use - Example - Value error

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.

VBA Date Value - Use - Example - Single quote

After entering the single quote before the date, press the Enter key to convert the value in cell A2 to text value.

VBA Date Value - Use - Example - text

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.

Use - Example 1

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.

VBA Date Value - Use - Example 1- False

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.

Use - Example 1 - Step 1

Step 2: Next, define a variable to store the data of cell A2 in the form of date.

Use - Example 1 - Step 2

Step 3: Then, assign the value of cell A2 to this variable.

VBA Date Value - Use - Example 1 - Step 3

Step 4: Now, let’s store this variable name in cell A3.

VBA Date Value - Use - Example 1 - Step 4

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;

VBA Date Value - Use - Example 1 - Step 4 - True

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.

Use - Example 2

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.

Use - Example 2 - 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.

VBA Date Value - Use - Example 2 - False

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.

VBA Date Value - Use - Example 2 - True

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.

Example 1

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.

VBA Date Value - Example 1 - Code

Now, run the code and we will get the following result in the worksheet.

VBA Date Value - Example 1 - Output

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.

SettingDescription
yyyyYear
qQuarter
mMonth
yDay of year
dDay
wWeekday
wwWeek
hHour
nMinute
sSecond

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.

Example 1

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.

Example 2 - Output

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)

1. What is the difference between CDate and DateValue in VBA

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.

FAQ 1 - DateValue Output

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.

FAQ 1 - CDate Output.jpg

CDate function gives us both date and time.

2. Why is VBA Date Value Function Not Working?

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.

3. What is the use of VBA DateValue?

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *