How to use DateValue Function in Excel VBA

Welcome to Excel Avon

DateValue Function in Excel VBA

Today’s article is about Date Value Function in VBA, today we are going to understand how Date Value Function works, DATEVALUE Function in Excel converts the dates present in text format to the standard short date format available in Excel. Suppose if we store “25 March 2010” as text date, then DateValue function will convert it to “25 /03/2010”, which is purely a date format in excel. For this, we need to supply the text date in double inverted commas to the DateValue function’s syntax. So we need to check by formatting the cells to get an exact result.

To understand the function, look at the below syntax of the function.

Datevalue(Date_text)

Arguments: –

Date_Text: – This is a required argument. It is the text that represents a date in an Excel date format,

To understand for DateValue Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

datevalue-in-excel-VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

datevalue-function-in-Excel-VBA

To understand DateValue Function in Excel VBA, we will write a subroutine for DateValue Function in Excel VBA after inserting the module in VBE.

Sub UseDateValue()

End Sub

After writing the subroutine we will define the variable

Sub UseDateValue()
Dim Inpstr as String
Dim Result as Variant
End Sub

Now we will store the date in InpStr

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant

InpStr = "May-6 2022" 
End Sub

Now we will use DateValue function with Date_text. date_text we store in InpStr

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant

InpStr = "May-6 2022"
Result = Datevalue(Inpstr)
End Sub

Use debug. Print for printing result

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant

InpStr = "May-6 2022"
Result = Datevalue(Inpstr)
debug. Print Result
End Sub

Click Run button,

Datevalue-function-in-excel-vba

After running the code, we get the result which is printed in date system format “dd-mmm-yy”

Datevalue-function-in-excel-v1

Now I will Store date in Another date format “dd mmm yyyy” and print the result

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant 

InpStr = "21 Jan 2022"
Result = Datevalue(Inpstr) 
debug. Print Result 
End Sub

Click Run button, When the date will be printed after the code is run, it will be in the system format “dd-mmm-yy‘ as you can see in the image below.

Datevalue-function-in-excel-v2

Now I will Store date in Another date format “mm dd yyyy” and print the result

Sub UseDateValue()
Dim Inpstr as String 
Dim Result as Variant 

InpStr = "12 28 2022" 
Result = Datevalue(Inpstr) 
debug. Print Result 
End Sub

To run the code, click on the run button, when the date is printed, it will be in the system format only.

Datevalue-function-in-excel-v3

Again, I will store date in inpstr but this time we didn’t make any format Because day above 12 shows that it is the month.

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant 

InpStr = "12 6 2022" 
Result = Datevalue(Inpstr) 
debug. Print Result 
End Sub

Click Run button,

Datevalue-function-in-excel-vba

You can see in the image below that the date printed is in the system format.

Datevalue-function-in-excel-v4

Again, store the date in inpstr with the new date format “yyyy-mm-dd” and print the result.

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant 

InpStr = "2022-06-23" 
Result = Datevalue(Inpstr) 
debug. Print Result 
End Sub

We store the date in any format, but when we print the result, we will get the same result which format is being used in our system. click run button and see the result.

Datevalue-function-in-excel-v5

Again, let’s change the format of store date, well if year is first in date format, then month is followed by date, but we will store date after year and month at last in “yyyy-dd-mm”.

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant 

InpStr = "2022-23-06" 
Result = Datevalue(Inpstr) 
debug. Print Result 
End Sub

Click on run button oh here is error because day comes after month but here, we have stored day before month.

Datevalue-function-in-excel-v6

Again, store the date in inpstr with the new date format “yyyy-mm-dd” and print the result.

Sub UseDateValue() 
Dim Inpstr as String 
Dim Result as Variant 

InpStr = "2022-06-23" 
Result = Datevalue(Inpstr)

debug. Print Result 
End Sub

And now we will print the result, this time the error was not given, but the result was printed in the immediate window.

Datevalue-function-in-excel-v7

So, I hope you have understood How to use format Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

LEARN MORE TOPIC IN VBA HERE