How to Use IsDate Function in VBA (3 Examples)

The IsDate function is a built-in VBA Information function in Excel that you can use in a lot of scenarios. Besides, you can perform various calculations with this. For that reason, it will save you a lot of time. In this tutorial, you will learn every detail of the IsDate function of VBA in Excel. This tutorial will be on point with suitable examples and proper illustrations.

The above screenshot is an overview of the article which represents a simplified application of the IsDate function in Excel. You’ll learn more about the dataset as well as the methods and usage in the following sections of this article.

Download Practice Workbook

Download this practice workbook.

Introduction to the IsDate Function

⏺ Function Objective

Recognize whether the given date is valid or not.

⏺ Syntax

⏺ Argument Explanation

expression: This is required. It is a variant data type that may be in the Date expression or String expression worthy of an acknowledgment as a date or time.

⏺ Returns

It returns a boolean value TRUE or FALSE.

⏺ Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

How to Use IsDate Function in VBA

Before you start with this function you have to first know about the VBA Date variables and also VBA Date functions.

Now, the IsDate function is pretty simple and easy to use in VBA. You have to enter a value as the argument of this function. After that, it will return TRUE or FALSE.

Take a look at the following code snippet:

Sub IsDate_Function()

Dim Date_value

Date_value = "04/04/2022"
MsgBox IsDate(Date_value)

Date_value = "9/33/2023"
MsgBox IsDate(Date_value)

Date_value = "January 31, 2014"
MsgBox IsDate(Date_value)

Date_value = "10.21.22"
MsgBox IsDate(Date_value)

Date_value = "09.22.2022"
MsgBox IsDate(Date_value)

Date_value = "9/12/2022 19:40:22"
MsgBox IsDate(Date_value)

End Sub

If you run the code, you will see the following output:

3 Practical Examples of IsDate Function in Excel VBA

In the following section, we are providing you with three suitable and effective examples of the IsDate function in VBA that you can implement into your dataset. Make sure you check them all. We recommend you learn and apply all these. It will surely enrich your Excel knowledge.

1. Find Valid or Invalid Date from a Range Using VBA IsDate

Now, in this example, we are finding the valid and invalid dates from a dataset. We are basically using the previous code but for a newly arranged dataset.

Take a look at the following dataset:

Here, you can see some dates in a column. Now, we will try to find out valid and invalid dates from here and write them into the adjacent cell.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• After that, type the following code:

Sub valid_date()

Dim cell As Range
Dim rng As Range
Set rng = Range("B5:B16")

For Each cell In rng
If IsDate(cell.Value) Then
  cell.Offset(0, 1) = "Valid Date"
Else
  cell.Offset(0, 1) = "Invalid Date"
 End If
Next cell


End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select valid_date.
• Then, click on Run.

As you can see, our IsDate function of VBA successfully recognized all the valid and invalid dates.

Read More: VBA Date Function (12 Uses of Macros with Examples)

2. Calculate Overdue Days for Valid Dates Using IsDate in VBA

The meaning of overdue is happening late, or past the deadline. Late, particularly, past a deadline or too late to meet a requirement.

Suppose you have to submit an assignment due Sunday. But it is Tuesday, and you haven’t submitted it. You can call it two overdue days.

Take a look at the following dataset:

Here, we have a dataset of some students and their assignment submission date. You can see the last date of submission. Our goal is to find the overdue date based on the submission date. Also, we used the IsDate function here to find the invalid date. Now, to accomplish this, follow these steps:

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• After that, type the following code:

Sub overdue_days()

Dim cell As Integer
Dim J As Integer
Dim due_date As Date
due_date = #1/11/2022#

For cell = 5 To 11
 If IsDate(Cells(cell, 4)) Then
   If Cells(cell, 4).Value = due_date Then
    Cells(cell, 5).Value = "Submitted Today"
   ElseIf Cells(cell, 4).Value > due_date Then
     J = due_date - Cells(cell, 4).Value
     J = Abs(J)
     Cells(cell, 5).Value = J & " Overdue Days"
   Else
    Cells(cell, 5).Value = "No Overdue"
   End If
  Else
   Cells(cell, 5).Value = "Invalid Date"  
 End If
Next cell

End Sub

We used the ABS function to remove the minus sign.

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select overdue_days.
• Then, click on Run.

As you can see, we have successfully used the IsDate function here in VBA and found both overdue days and invalid dates.

Read More: How to Use VBA DateDiff Function in Excel (9 Examples)

Similar Readings:

• How to Use VBA Abs Function in Excel (9 Examples)
• Use Concatenate in Excel VBA (4 Methods)
• How to Use VBA Randomize Function in Excel (5 Examples)
• Use VBA FileDateTime Function in Excel (3 Uses)
• How to Use VBA MkDir Function in Excel (6 Examples)

3. Add Days in a Date If Date is Valid Using VBA

Now, you can define the date variable and use it to add the date. To perform this, we are using the DateAdd method of VBA. You can use this function to add days, months, and years to a particular date. Also, we are using the IsDate function to find the invalid date from the dataset.

Take a look at the following dataset:

Here, you can see some names and some dates attached to them. Now, our goal is to add five more days to these given dates generate a new date, and find the invalid date.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type the following code:

Sub add_days()

Dim first_date As Date
Dim second_date As Date
Dim cell As Integer

For cell = 5 To 11
  If IsDate(Cells(cell, 3).Value) Then
   first_date = Cells(cell, 3).Value
   second_date = DateAdd("d", 5, first_date)
   Cells(cell, 4).Value = second_date
  Else
   Cells(cell, 4).Value = "Not a Valid Date"
  End If
Next cell

End Sub

Here, we used “d” as an argument in the DateAdd function. You can change it to “y” or “m” to add years or months respectively,

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select add_days.
• Then, click on Run.

Here, November 31, 2022, is invalid because there are only 30 days in November.

As you can see, we have successfully added days into a date and found the invalid date using the IsDate function in VBA. Now, you can modify the code according to your choice.

Related Content: How to Use VBA IsNumeric Function (9 Examples)

💬 Things to Remember

✎ IsDate function is only available as a  VBA function.

✎ Remember, Date is a very tactful thing in Excel.

✎ In Windows, the range of valid dates is January 1, 100 A.D., through December 31, 9999 A.D.; the ranges differ among operating systems.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the VBA IsDate function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• VBA If – Then – Else Statement in Excel (4 Examples)
• VBA EXP Function in Excel (5 Examples)
• How to Use VBA Mod Operator (9 Examples)
• Use the VBA Weekday Function (2 Suitable Examples)
• How to Use Log Function in Excel VBA (5 Suitable Examples)