VBA IsDate Function

What is Excel VBA IsDate Function?

The VBA IsDate function can be used to determine if an expression is a valid date or not. Depending on whether the expression can be transformed into a legitimate date, it returns a Boolean value (True or False). This feature helps make sure that date values are formatted correctly before processing, which is especially helpful when working with user input or data imports. Let us look at an example. We have a VBA subroutine named CheckDateValidity. It is used to determine whether or not a date entered by the user can be interpreted as valid.

When the code runs, an input box is displayed to the user, prompting them to enter a date, which is then saved in the userInput variable. The code then makes use of the VBA IsDate function to determine if the value entered in userInput is a valid date.

The VBA IsDate function, designed for this purpose, returns either True if the input is recognized as a valid date or False if it is not.

Finally, the evaluation’s outcome is shown to the user in a message box, giving them instant feedback. To facilitate the speedy validation of date inputs within Excel VBA, the message box will display “True” if the input is a valid date and “False” if it is not.

Syntax

The syntax of the IsDate function is:

IsDate(Expression)

Expression: It is this value or expression that you wish to verify as a legitimate date. It is the only component of the function that is necessary. A cell reference, a variable, or a constant can be used as the expression.

In simpler terms:

• IsDate: The name of the function.
• Expression: The value or data you want to check if it’s a valid date.

The expression is evaluated by the VBA IsDate function, which returns “True” if the expression can be understood as a valid date and “False” otherwise.

How to Use VBA IsDate Function?

Using the IsDate function in VBA is straightforward. Here are the steps to follow:

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: In the new module, create a subroutine and declare any variables you’ll use. In the case of the VBA IsDate function, you typically declare a variable to store the user input or the value you want to check as a date.

For example:

Dim userInput As String

Step 4: Assign a value to the variable you declared. This value is usually obtained from user input or some data source.

Here’s an example using an input box in VBA to get user input:

userInput = InputBox(“Enter a date:”)

Step 5: Use the VBA IsDate function to check whether the value in your variable is a valid date or not.

For example:

If IsDate(userInput) Then

Step 6: You can code what should happen if the value is a valid date (True condition) and what should happen if it isn’t (False condition) inside the If statement.

For example:

    MsgBox userInput & ” is a valid date.”

Else

    MsgBox userInput & ” is not a valid date.”

End If

Step 7: Save your workbook and close the VBA editor. You can run the code by either pressing F5 or by clicking “Run.”

Examples

Example #1 – Basic Date Validation

In this example, we check if the string “12/31/2023” is a valid date, which it is, so the message “12/31/2023 is a valid date” will be displayed.

Step 1: In the new module, we first declare a VBA subroutine named BasicDateValidation.

Step 2: Next, we declare a variable named dateValue as a VBA String data type. This variable will store the date we want to check. In this case, we assign the value “12/31/2023” to dateValue.

Step 3: This line begins an If statement. It checks whether the expression IsDate(dateValue) evaluates to True. IsDate is a function that checks if the value in dateValue is a valid date.

Step 4: If the condition in the If statement is True (i.e., dateValue is a valid date), this code block is executed. It displays a message box with the message indicating that dateValue is a valid date.

Step 5: This code block is executed if the If statement’s condition is False, meaning that dateValue is an invalid date. DateValue is not a valid date, as indicated by the message box that appears.

Step 6: Now, save the VBA macro and click on run. When this code is executed, it determines whether the string “12/31/2023” is a valid date and shows a message box with that information.

Here is the complete code:

Sub BasicDateValidation()

    Dim dateValue As String

    dateValue = “12/31/2023”

    If IsDate(dateValue) Then

        MsgBox dateValue & ” is a valid date.”

    Else

        MsgBox dateValue & ” is not a valid date.”

    End If

End Sub

Example #2 – Invalid Date Check

In this example, we intentionally use a date that is not valid in the default system format (“31/21/2023”). The IsDate function will return False, and the message “31/21/2023 is not a valid date” will be displayed.

Step 1: We declare a new subroutine named InvalidDateCheck to check whether a given date (in an invalid format) is considered valid.

Step 2: Next, we declare a variable invalidDate to store the date to be checked. In this case, we assign the value “31/21/2023” to invalidDate, which is in a format that is not valid in the default system format.

Step 3: Here, we start an If statement that checks whether the expression IsDate(invalidDate) is True. It evaluates if invalidDate can be interpreted as a valid date.

Step 4: If the condition in the If statement is True, then this code block is executed. It displays a message box with a message indicating that invalidDate is a valid date.

Step 5: If the condition in the If statement is False, this code block is executed. It displays a message box with the message indicating that invalidDate is not a valid date.

Step 6: Now, when you run this code, it determines whether the string “31/21/2023” is a valid date based on the output of the VBA IsDate function and shows a message box stating whether it is or is not.

Here is the full code:

Sub InvalidDateCheck()

    Dim invalidDate As String

    invalidDate = “31/21/2023”

    If IsDate(invalidDate) Then

        MsgBox invalidDate & ” is a valid date.”

    Else

        MsgBox invalidDate & ” is not a valid date.”

    End If

End Sub

Example #3 – Date with Time

In this example, we check if the string “12/31/2023 14:30:00” represents a valid date and time. The IsDate function will return True, as it can handle both date and time components.

Step 1: Firstly, we declare a new subroutine named “DateWithTimeCheck.”

Step 2: Next, we declare a variable dateTimeValue to store the date and time to be checked. In this case, we assign the value “12/31/2023 14:30:00” to dateTimeValue.

Step 3: This line starts an If-Else statement in VBA that checks whether the expression IsDate(dateTimeValue) is True. It checks if dateTimeValue can be interpreted as a valid date and time.

Step 4: If the condition in the If statement is True, this code block is executed. It displays a message box with the message indicating that dateTimeValue is a valid date and time.

Step 5: If the condition in the If statement is False, this code block is executed. However, it should not be reached in this specific case since we have provided a valid date and time.

Step 6: Now, save the code and run.

When this code runs, it determines whether the string “12/31/2023 14:30:00″—which contains both the date and the time components—is a valid date and time. Based on the output of the VBA IsDate function, it displays a message box that indicates whether or not the date and time is valid.

Here is the full code:

Sub DateWithTimeCheck()

    Dim dateTimeValue As String

    dateTimeValue = “12/31/2023 14:30:00”

       If IsDate(dateTimeValue) Then

        MsgBox dateTimeValue & ” is a valid date and time.”

    Else

        MsgBox dateTimeValue & ” is not a valid date and time.”

    End If

End Sub

Important Things To Note

1. The VBA IsDate function uses the default date format of the system. Ensure that the expression you’re checking matches this format.
2. The behavior of the VBA IsDate function can be influenced by regional settings that set the separators and date format. When working with data from different locations, be mindful of possible differences.
3. VBA IsDate Function can handle both date and time components in the expression.
4. While the VBA IsDate function helps identify valid dates, it doesn’t validate the logical correctness of dates (e.g., February 30th). Additional validation may be necessary for specific date rules.
5. To avoid incorrect date recognition which is a common cause of the VBA IsDate function not working error, the date format of the input value must match the system’s default date format.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA IsDate Function. Here we learn How to Use VBA IsDate Function using syntax along with examples. You can learn more from the following articles –

• VBA DateValue
• VBA DateAdd Function
• VBA DateDiff Function