Excel VBA If Not IsDate: Check Dates in Excel Cells

VBA & macros · Updated November 21, 2026

In Excel VBA, use If Not IsDate(value) Then to run code when a value is not recognized as a date or time. For a worksheet cell, read its value into a variable first, check for an Excel error value if necessary, and then use IsDate; remember that numeric values and text dates can have surprising results.

What If Not IsDate means

IsDate is a VBA function that returns True when an expression can be interpreted as a date or time, and False when it cannot. The word Not reverses that result. So If Not IsDate(inputValue) Then means “if this value is not recognized as a date or time, run the following statements.” It is ordinary VBA conditional logic, not a special Excel worksheet formula.

Use a block If statement when invalid data needs more than one response:

If Not IsDate(inputValue) Then
    MsgBox "Enter a valid date."
    Exit Sub
End If

The example shows the basic syntax, but a complete procedure also needs declared variables and a place to obtain the value. In a real macro, decide whether you want to reject a value, flag it for review, ask the user to correct it, or skip it. A successful IsDate check only answers whether VBA can interpret the value; it does not establish that the date makes sense for your business rules.

A basic check on a worksheet cell

This example reads A2 on a worksheet named Sheet1 and reports whether VBA accepts its value. It checks for an Excel error before calling IsDate, because a cell containing an error such as #N/A is not ordinary date input.

Sub CheckA2ForDate()
    Dim ws As Worksheet
    Dim inputValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    inputValue = ws.Range("A2").Value

    ' Check cell errors before checking date interpretation.
    If IsError(inputValue) Then
        MsgBox "A2 contains an Excel error."
    ElseIf Not IsDate(inputValue) Then
        MsgBox "A2 is not recognized as a date or time."
    Else
        MsgBox "VBA recognizes A2 as a date or time."
    End If
End Sub

Change Sheet1 and A2 to match the workbook. A Variant is useful here because a cell may return a date, number, text, blank, or error value. The code tests the value rather than the cell’s displayed appearance: formatting a cell to look like a date does not, by itself, prove the underlying value is a valid date.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even when the macro makes an unintended change. On Windows, open the VBA editor with Alt+F11; on Mac, the editor shortcut differs by keyboard and Excel setup, and can be opened through the Developer tab if enabled. VBA macros cannot run in Excel for the web.

Handle blanks and Excel errors deliberately

A blank cell is usually not a date, so Not IsDate will generally send it down the invalid branch. That may be exactly what a required-date field needs. For an optional field, however, treating an empty cell as an error can create noisy warnings. Check for blank input separately, before the date test, and decide how to treat a formula that returns an empty string as well.

The following order distinguishes cell errors, blank-looking input, and values that fail the date test. It handles a cell containing a formula that displays an empty string by trimming its converted text.

Sub ClassifyA2()
    Dim ws As Worksheet
    Dim inputValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    inputValue = ws.Range("A2").Value

    ' Give errors and blanks their own outcomes.
    If IsError(inputValue) Then
        MsgBox "A2 contains an Excel error."
    ElseIf Len(Trim$(CStr(inputValue))) = 0 Then
        MsgBox "A2 is blank."
    ElseIf Not IsDate(inputValue) Then
        MsgBox "A2 is not a recognized date or time."
    Else
        MsgBox "A2 passes the date interpretation check."
    End If
End Sub

For an ordinary worksheet cell, CStr can convert its value to text for this blank check. Keeping the IsError branch first matters: converting an error value to text this way is not a safe substitute for checking the error. If your input can come from sources other than worksheet cells, such as a database or a user-defined procedure, handle possible Null values according to that source’s behavior before converting or testing them.

How IsDate interprets text and regional settings

IsDate accepts values VBA can interpret as dates or times, including many date-like text strings. Interpretation can depend on the computer’s regional date settings. For example, a string such as 04/05/2026 can mean April 5 or May 4 depending on the date convention in use. A value accepted on one computer might therefore be interpreted differently on another.

This is an important limitation when importing files, processing text from another system, or sharing a workbook across regions. Do not assume that IsDate validates a particular written format. If the required format is unambiguous, parse and validate its parts according to that format, or arrange for the source data to use a consistent format such as year-month-day. Avoid relying on a display format alone: formatting changes how Excel shows a value, not necessarily how VBA interprets text supplied to it.

Also distinguish between a date and a time. IsDate can accept a time-only value, so a check may return True for input that has no calendar date component. If a field must contain a date rather than a time, IsDate alone does not enforce that requirement. Add a separate rule for the permitted range or format, and test that rule using data appropriate to your workflow.

Why numbers can pass the check

Excel stores dates as serial numbers, with the displayed date produced by number formatting. As a result, a value that looks like an ordinary number can sometimes be interpreted by VBA as a date or time. Conversely, a cell formatted with a date number format can still contain a number that is not a meaningful date for your application. IsDate tests interpretability, not the cell’s number format or the user’s intention.

This is why replacing IsDate with a simple check of the cell’s number format is not a reliable fix. A format such as a date pattern controls appearance; it does not turn arbitrary content into a meaningful calendar date. Excel workbooks can also use different date systems, so avoid hard-coding serial-number assumptions without accounting for the workbook and the source of the data.

If users enter dates into a controlled form, define what counts as valid before writing the macro. For example, a delivery date might need to be between today and a deadline, while a historical record might allow earlier dates. Use IsDate as the first interpretation check, then apply the relevant business rules. Do not reject a number merely because it is numeric if your source intentionally supplies Excel date serials; instead, define and verify the accepted input type and range.

Validate a column and flag invalid entries

For a list of dates in column A, it is often more useful to mark each result than to display a message for every row. This procedure checks from A2 to the last used cell in column A and writes a status in column B. It treats empty cells as blank, Excel errors as errors, and other values according to IsDate.

Sub FlagDateValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim inputValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Review each populated row and write a status beside it.
    For Each cell In ws.Range("A2:A" & lastRow)
        inputValue = cell.Value

        If IsError(inputValue) Then
            cell.Offset(0, 1).Value = "Cell error"
        ElseIf Len(Trim$(CStr(inputValue))) = 0 Then
            cell.Offset(0, 1).Value = "Blank"
        ElseIf Not IsDate(inputValue) Then
            cell.Offset(0, 1).Value = "Invalid date"
        Else
            cell.Offset(0, 1).Value = "Recognized"
        End If
    Next cell
End Sub

This example overwrites the corresponding cells in column B, so check that the destination is available before running it. If column A has no data below its header, the loop has no rows to process. The macro labels accepted values “Recognized,” not “Correct”: a value can pass IsDate and still violate your required date range or represent a time when the field requires a calendar date. For a large data set, consider whether you want to write statuses to a separate review column rather than changing source data.

Common mistakes and better validation choices

  • Checking the display instead of the value: A cell that appears date-formatted may contain an unexpected number. Test the value and the rules that matter to the task.
  • Using IsDate as a format validator: It does not guarantee a specific text pattern, such as day/month/year. Enforce the required format separately when consistency matters.
  • Forgetting time-only input: IsDate can recognize times as well as dates. Add a further check if the field must represent a calendar date.
  • Ignoring errors and optional blanks: Check Excel error values first, then decide whether blanks should be allowed, flagged, or rejected.
  • Assuming every numeric value is invalid: Excel date values are numeric serials. Determine what the source is supposed to provide rather than judging from the value’s appearance.

For user entry, Excel’s Data Validation feature can guide users before a macro processes the data. On Microsoft 365 for Windows, use Data > Data Validation to set an allowed date range or other input rule. This helps prevent some mistakes, but it does not replace checking imported or pasted data in VBA. On Mac, the Data Validation command is also available from the Data tab, though the ribbon layout can differ. Excel for the web has data-validation tools, but it cannot run the VBA procedure itself.

Frequently Asked Questions

What is the correct VBA syntax for “if not a date”? Use If Not IsDate(value) Then, followed by the statements to run when the value is not recognized. Close a multiline block with End If.

Does IsDate return False for a blank cell? A blank cell is generally not recognized as a date, so the invalid branch is reached. If blanks are allowed, test for blank input separately so you can report it as optional rather than invalid.

Can IsDate accept a time without a date? Yes. It tests for a date or time expression. If the field must contain a calendar date, add a rule that distinguishes the required input from time-only values.

Why does IsDate behave differently on another computer? Regional date conventions can affect how date-like text is interpreted. Ambiguous strings such as 04/05/2026 may be read differently, so use a consistent input format or parse the expected format explicitly.

Does a date-formatted cell guarantee IsDate returns True? No. Formatting controls how a value appears; it does not guarantee that the underlying value is a meaningful date. Check the underlying value and any application-specific range or format requirements.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.