EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Date & Time Functions VBA DateValue
Secondary Sidebar
VBA Date & Time Functions
  • VBA Date and Time
    • VBA Month
    • VBA DateSerial
    • VBA Date Format
    • VBA Time
    • VBA TIMER
    • VBA Date
    • VBA Format Number
    • VBA DateAdd
    • VBA DateValue
    • VBA DatePart
    • VBA DateDiff
    • VBA Format Date

VBA DateValue

By Madhuri ThakurMadhuri Thakur

VBA DateValue

VBA DateValue Function

While working in VBA we work with dates. We assign dates to various variables or we can use the DATEVALUE function to do so. DATEVALUE is both worksheet and Visual Basic function. In worksheet, it gives a serial date. When we use this function in VBA it gives us a date value. As I told above the DATEVALUE function used in VBA returns a date value. The data type we used for our variables while using this function is date. It is somewhat similar to the worksheet function. How we use this function is pretty simple, it takes a date as an argument.

Syntax of DateValue in Excel VBA

The syntax for the VBA DateValue Function in excel is as follows:

Syntax of VBA DateValue

What this function does that it takes a value or an argument as a string and returns it to a date. Why we use this function is because we work with lots of data and every human being is not similar so there will be a difference in entering date formats for each and every different person. If we have to do some calculations over the dates on the data then we will have to convert every different format of the dates available in the data onto one single format and then perform the calculation. Now imagine the large chunk of data with a large chunk of different types of dates.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Let us look by a few examples of how we can use this DATEVALUE function in VBA through some examples which will give us some more clear idea on how this function works.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,580 ratings)

How to Use VBA DateValue in Excel?

We will learn how to use a VBA DateValue Function with few examples in excel.

You can download this VBA DateValue Excel Template here – VBA DateValue Excel Template

Example #1 – VBA DateValue

Let us begin with a very simple step. We will assign a variable a date value as a string and then print the date value for that variable.

Step 1: Now once we are inside the editor we will have a lot of options in the header, from the insert tab insert a new module in the editor where we will perform all the example demonstrations,

DateValue Module

Step 2: Now let us start our procedure as shown below.

Code:

Sub Sample()

End Sub

Datevalue Example 1.1

Step 3: Let us declare a variable as Date data type as shown below,

Code:

Sub Sample()

Dim A As Date

End Sub

Datevalue Example 1.2

Step 4: Now let us provide variable A with some value using the DATEVALUE function as shown below.

Code:

Sub Sample()

Dim A As Date
A = DateValue("02/02/2019")

End Sub

vba datevalue function Example 1.3

Step 5: I have provided the date in double inverted commas to treat it as a string, now use the msgbox function to display the value stored in A.

Code:

Sub Sample()

Dim A As Date
A = DateValue("02/02/2019")
MsgBox A

End Sub

vba datevalue function Example 1.4

Step 6: Execute the above code and see the result.

VBA DateValue 1

We know that in VBA date is provided in # symbols and a string is provided in “ double quotes. But DATEVALUE changes the value to date.

Example #2 – VBA DateValue

Now let us take an input from a user in date because a user can provide a date in any format. We will convert the value given by the user in the date using the DATEVALUE function.

Step 1: From the Insert, select a new module in the editor.

vba datevalue function Module 2

Step 2: Now let us start another subprocedure as follows.

Code:

Sub Sample1()

End Sub

Datevalue Example 2.1

Step 3: Declare two variables as dates as we will take value from input from user in one variable while in another we will use the DATEVALUE function to convert it to a date.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date

End Sub

Example 2.2

Step 4: In the input data variable take the input from the user using the input box function.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date
InputDate = InputBox("Enter a Date")

End Sub

Example 2.3

Step 5: In the Outputdate variable let us change the date we get as input to date using the DATEVALUE function as shown below.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date
InputDate = InputBox("Enter a Date")
OutputDate = DateValue(InputDate)

End Sub

Example 2.4

Step 6: Now let us display the value we have now using the msgbox function.

Code:

Sub Sample1()

Dim InputDate As Date, OutputDate As Date
InputDate = InputBox("Enter a Date")
OutputDate = DateValue(InputDate)
MsgBox OutputDate

End Sub

Datevalue Example 2.5

Step 7: Run the above code and give an input date as follows.

Output 1

Step 8: Click on OK and see the final result as follows.

Output 2

Although the date format from the user was different still we have successfully converted it into a date.

Example #3 – VBA DateValue

We can see dates in our sheet and how to use DATEVALUE function on them? I have a date in cell A1 as follows.

Datevalue Example 3.1

Let us change it to the standard date we have.

Step 1: Again in the same module start another subprocedure as shown below.

Code:

Sub Sample2()

End Sub

vba datevalue function Example 3.2

Step 2: Declare a variable as Date as shown below.

Code:

Sub Sample2()

Dim Dt As Date

End Sub

Example 3.3

Step 3: Now in the Dt variable, store the value of the cell A1 which is as follows using the DATEVALUE function.

Code:

Sub Sample2()

Dim Dt As Date
Dt = DateValue(Sheet1.Range("A1").Value)

End Sub

Example 3.4

Step 4: Now let us display the value stored in our variable.

Code:

Sub Sample2()

Dim Dt As Date
Dt = DateValue(Sheet1.Range("A1").Value)
MsgBox Dt

End Sub

vba datevalue function Example 3.5

Step 5: Now execute the above code and let us see the result.

VBA DateValue 2

Things to Remember

  • DATEVALUE is a date function in VBA.
  • It is somewhat similar to the worksheet function, worksheet function gives us the serial numbers of the date while in VBA it gives us the DATEVALUE.
  • This function takes the argument as the date in the string and converts it in the string.

Recommended Articles

This is a guide to VBA DateValue. Here we discuss how to use Excel VBA DateValue Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA Date Format
  3. VBA Subscript out of Range
  4. VBA Web Scraping
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more