WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA IsDate Function

VBA IsDate Function

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA IsDate Function

IsDate is the VBA function which tests whether the given value is the date or not. If the supplied value or range reference value is date value then we will get the result as “TRUE”, if the value is not date value then we will get the result as “FALSE”. So, the result is BOOLEAN value i.e. either TRUE or FALSE.

Below is the syntax of the IsDate function.

VBA IsDate Formula

The expression is nothing but the value that we are trying to test, whether it is the date or not.

How to use VBA IsDate Function?

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

We will test whether the value “5.01.19” is a date value or not.

For this first start, the excel macro procedure.

Code:

Sub IsDate_Example1()

End Sub

Example 1

Define the variable to store the date value, and since the value will be date value, assign the data type as “Date” only.

Code:

Sub IsDate_Example1()

Dim MyDate As Date

End Sub

Example 1.1

Now assign the value of “5.1.19” to the variable “MyDate.”

Code:

Sub IsDate_Example1()

Dim MyDate As Date

MyDate = "5.1.19"

End Sub

VBA IsDate Example 1.2

Open the message box in VBA now.

Code:

Sub IsDate_Example1()

Dim MyDate As Date

MyDate = "5.1.19"

MsgBox(

End Sub

 Example 1.3

In this message box, we will test whether the supplied date value to the variable “MyDate” is the date or not by using the “IsDate” function. First, open the “IsDate” function.

Code:

Sub IsDate_Example1()

Dim MyDate As Date

MyDate = "5.1.19"

MsgBox IsDate(

End Sub

VBA IsDate Example 1.4

The expression is the value we are testing to find whether it is Date or not. Since we have already stored the value to the variable “MyDate,” supply the variable name only.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub IsDate_Example1()

Dim MyDate As Date

MyDate = "5.1.19"

MsgBox IsDate(MyDate)

End Sub

 Example 1.5

Ok, now run the code and see what we get in the message box.

VBA IsDate Example 1.6

Wow!!! The result is TRUE.

You must be wondering how it recognized the value “5.1.19” as the date.

The reason why it has returned the result as TRUE because when you look at the given value “5.1.19,” it is the short form of the date “05.01.2019,” so excel is brilliant enough to recognize it as date, so the result is TRUE.

 Example 1.7

Now here comes the tricky thing, for the same value, what we will do is we will change the short form of the year from 19 to 2019.

Code:

Sub IsDate_Example1()

Dim MyDate As String

MyDate = "5.1.2019"

MsgBox IsDate(MyDate)

End Sub

Example 2.0

Now run the code and see the result.

VBA IsDate Example 2.1

This time it has returned the result as FALSE because the “day and month” portion of the date is in short form, but the year part is in full form of “YYYY,” so ISDATE cannot recognize it has a date, so the result is FALSE.

Now, look at the below code.

Code:

Sub IsDate_Example1()

Dim MyDate As String

MyDate = "05.01.2019"

MsgBox IsDate(MyDate)

End Sub

 Example 2.2

I have mentioned a full day and full month format by using 0. Let’s run the code and see the result of the IsDate function.

This time also we go got the result as FALSE.

VBA IsDate Example 2.3

Now change the code as follows.

Code:

Sub IsDate_Example1()

Dim MyDate As String

MyDate = "05/01/2019"

MsgBox IsDate(MyDate)

End Sub

 Example 2.4

Instead of the dot (.) as the separator, we have entered forward-slash (/) as the separator. Now run the code and see the result.

VBA IsDate Example 2.5

This time we got the result as TRUE.

This is the reason I have told you at the beginning of the article that “Date” is a sensitive thing.

Now what I will do is I will merge the date and time together.

Code:

Sub IsDate_Example1()

Dim MyDate As String

MyDate = "05/01/2019 15:26:24"

MsgBox IsDate(MyDate)

End Sub

 Example 2.6

What I have added above is the time portion of “15:26:24” in front of the date. Now run the code and see the result.

VBA IsDate Example 2.7

This time too, we got the result as TRUE because DATE & TIME in excel are the same things and stored as serial numbers. The whole number represents the date portion, and decimal places represent the time portion.

Things to Remember here

  • IsDate returns the Boolean type result, i.e., either TRUE or FALSE.
  • IsDate is available only as a VBA function.
  • Only valid formatted dates are treated as the date, or else it will treat as text values and return the result as FALSE.

Recommended Articles

This has been a guide to VBA IsDate. Here we discuss how to use the VBA IsDate function to check whether a given value is a date or not, along with the example and downloadable excel sheet. Below are some useful excel articles related to VBA –

  • VBA Randomize
  • Steps to Create Login Form in VBA
  • VBA Month
  • DatePart Function in VBA
  • VBA RegEx
10 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA IsDate Excel Template

Coursera IPO Financial Model & Valuation Free Download