Table of Contents
The DateValue function in VBA is a useful tool for converting a date in string format into a date value that can be used in calculations and comparisons. It allows for easy manipulation of dates within VBA code. To use the DateValue function, simply enter the date in a valid string format, such as “MM/DD/YYYY”, as the argument. This will return the corresponding date value. For example, if the string “10/25/2021” is passed as an argument, the function will return the date value 10/25/2021. This can then be stored in a variable or used in any other VBA code as needed.
Use DateValue Function in VBA (With Example)
You can use the DateValue function in VBA to return the date value from a given string.
Here is one common way to use this function in practice:
Sub GetDateValue()
Dim i As Integer
For i = 2 To 7
Range("B" & i) = DateValue(Range("A" & i))
Next i
End Sub
This particular macro will extract the date value from the datetimes in the range A2:A7 and return this date value in the corresponding range of B2:B7.
The following example shows how to use this syntax in practice.
Example: How to Use DateValue Function in VBA
Suppose we have the following column of datetimes in Excel:
Suppose we would like to extract the date from each datetime in column A and display it in column B.
We can create the following macro to do so:
Sub GetDateValue()
Dim i As Integer
For i = 2 To 7
Range("B" & i) = DateValue(Range("A" & i))
Next i
End Sub
When we run this macro, we receive the following output:
Column B shows the date value for each datetime in column A.
For example:
- The DateValue function returns 1/1/2023 from 1/1/2023 10:15:34 AM
- The DateValue function returns 1/3/2023 from 1/3/2023 12:34:18 PM
- The DateValue function returns 1/5/2023 from 1/5/2023 8:23:00 AM
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: