Excel VBA InputBox for Dates: How to Validate Input

VBA & macros · Updated November 21, 2026

To ask for a date in Excel VBA, collect the entry as text, validate it, and convert it to a VBA Date value before using it. For dependable results across regional settings, request a fixed format such as YYYY-MM-DD; Excel’s standard InputBox does not provide a built-in calendar picker or automatically guarantee that the response is a valid date.

What a VBA InputBox returns

The VBA InputBox function displays a dialog and returns the user’s response as a string. Even if someone types something that looks like a date, the return value is text until your code checks and converts it. A basic example is answer = InputBox("Enter a date:"), but accepting that answer without validation can lead to errors or a date interpreted differently than intended.

Excel also has Application.InputBox, a method that can restrict the kind of input it accepts. Setting Type:=2 requests text, which is useful when you want to validate a date string yourself. Its Cancel button returns the Boolean value False, whereas the VBA InputBox function returns a zero-length string when canceled. That difference matters if an empty response and Cancel need to be handled separately.

Neither version should be confused with a date-specific control. A standard VBA InputBox does not turn a text box into a calendar, and typing a date does not by itself prove that the entry is valid.

A reliable example for YYYY-MM-DD

This macro asks for a date in year-month-day order, rejects malformed or impossible dates, then writes the result to cell B2 on the active worksheet. It also distinguishes Cancel from an empty response. The strict format avoids ambiguity between entries such as 03/04/2026, which can mean different dates in different regional settings.

Sub AskForISODate()
    Dim response As Variant
    Dim entered As String
    Dim yearPart As Long
    Dim monthPart As Long
    Dim dayPart As Long
    Dim parsedDate As Date

    ' Type 2 requests text; Cancel returns Boolean False.
    response = Application.InputBox( _
        Prompt:="Enter a date as YYYY-MM-DD:", _
        Title:="Date entry", _
        Type:=2)

    If VarType(response) = vbBoolean Then
        If response = False Then Exit Sub
    End If

    entered = Trim$(CStr(response))
    If Len(entered) = 0 Then
        MsgBox "No date was entered.", vbExclamation
        Exit Sub
    End If

    If (entered Like "####-##-##") = False Then
        MsgBox "Use the format YYYY-MM-DD, for example 2026-03-04.", vbExclamation
        Exit Sub
    End If

    yearPart = CLng(Left$(entered, 4))
    monthPart = CLng(Mid$(entered, 6, 2))
    dayPart = CLng(Right$(entered, 2))

    If yearPart < 100 Or yearPart > 9999 _
       Or monthPart < 1 Or monthPart > 12 _
       Or dayPart < 1 Or dayPart > 31 Then
        MsgBox "That date is outside the supported range.", vbExclamation
        Exit Sub
    End If

    parsedDate = DateSerial(yearPart, monthPart, dayPart)

    ' DateSerial rolls excess days into the next month, so compare parts.
    If Year(parsedDate) <> yearPart _
       Or Month(parsedDate) <> monthPart _
       Or Day(parsedDate) <> dayPart Then
        MsgBox "That is not a valid calendar date.", vbExclamation
        Exit Sub
    End If

    ActiveSheet.Range("B2").Value = parsedDate
    ActiveSheet.Range("B2").NumberFormat = "yyyy-mm-dd"
End Sub

The check after DateSerial is important. VBA’s DateSerial can normalize an out-of-range day into the following month: a date such as February 30 can roll into March rather than being rejected automatically. Comparing the resulting year, month, and day with the supplied components catches that case, including invalid leap-day entries.

Before running any macro, save a copy of the workbook. Macros can change cells and those changes cannot be reversed with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Option+F11, sometimes with Fn as well depending on the keyboard. Excel for the web can open a workbook containing VBA but cannot run VBA macros.

Why date format and regional settings matter

Dates written with slashes are not universally unambiguous. For example, 04/05/2026 can be read as April 5 or May 4 depending on the date conventions in use. The VBA conversion function CDate interprets text according to the system’s regional settings, so the same text can produce different results on different computers.

If you decide to accept a local date format instead, validate the response with IsDate before converting it with CDate. This catches many invalid strings, but it does not resolve the ambiguity of a valid string like 04/05/2026. Tell users which order to enter, or use a fixed format and parse its components as in the example. Avoid assuming that a date format displayed on one computer will be interpreted identically elsewhere.

Formatting and value are also different things. A VBA Date value written to a worksheet is stored by Excel as a date serial number; the cell’s number format controls how that value appears. Setting the cell format to YYYY-MM-DD makes the display consistent, but it does not change how the input text was interpreted. Validate first, then set the display format.

Handling Cancel, blank input, and invalid dates

Decide what each kind of response should mean before adding the InputBox to a larger macro. Cancel might mean stop the operation without changing anything. A blank entry might mean ask again, skip an optional date, or show an error. An invalid date should normally be rejected rather than silently converted.

  • Cancel: With Application.InputBox, check whether the returned Variant is Boolean before treating it as text. This catches its Cancel result, False.
  • Blank: After converting the text response to a string and trimming spaces, test whether its length is zero.
  • Malformed: Check the expected pattern before extracting the year, month, and day.
  • Impossible: Check the component ranges and verify the date created by DateSerial.
  • Optional entry: If blank is allowed, handle it deliberately and exit or continue without attempting to convert it.

Do not treat every response equal to zero as Cancel. For Application.InputBox, Cancel is a Boolean value, so checking the Variant’s type avoids confusing that result with text that a user entered. In the example, Type 2 means a user-entered word such as “False” is text, while clicking Cancel returns a Boolean.

Writing the result to a worksheet

Once validated, assign the Date value to the target cell rather than writing the original text. That makes the cell usable in date calculations, sorting, filtering, and date-based formulas. The example uses ActiveSheet.Range("B2") for brevity, but an active sheet can change. In a workbook used by other people, qualify the destination with the intended worksheet, for example a known sheet in that workbook, and confirm that the sheet name is correct.

Changing NumberFormat affects only the display. A format such as YYYY-MM-DD is helpful for reports and reduces confusion when someone reads the sheet, while the underlying value remains a date. If the destination cell is meant to preserve the exact characters someone typed rather than represent a working date, store text intentionally instead; that is a different requirement and will not behave like a normal date in calculations.

InputBox versus other ways to collect dates

A small macro that needs one date may be well served by an InputBox, especially when you want a simple prompt and strict validation. For repeated data entry or multiple users, other approaches can be easier to understand and maintain:

  • Worksheet cells with Data Validation: Select the input cells and use Data > Data Validation, then choose Date under Allow and set suitable limits. This gives users a visible place to enter values without running a macro. Validation is a helpful guardrail, but pasted data can bypass some worksheet validation behavior, so check important values in downstream processes too.
  • A custom VBA UserForm: A form can provide labeled fields and more tailored instructions. It requires more setup and code than a one-line prompt. A calendar control is not universally available across Excel installations, so a form should not depend on an ActiveX date picker without checking deployment compatibility.
  • Direct entry in the worksheet: For a simple workbook, a clearly labeled cell and instructions to enter dates in an agreed format may be enough. This avoids requiring macro-enabled files and is usable in Excel for the web.

Excel’s standard VBA InputBox is not a date picker. If a calendar interface is a firm requirement, choose a compatible custom form or another supported interface and test it on the versions and platforms where the workbook will be used.

Common mistakes and how to avoid them

  • Converting without checking: Calling CDate on arbitrary text can cause a type mismatch or accept a date according to local conventions. Validate before conversion.
  • Assuming DateSerial rejects every invalid day: It can roll extra days into another month. Compare the constructed date’s components with the requested components.
  • Confusing text display with a date value: A cell that looks like a date may contain text. Assign a validated Date value when calculations are needed.
  • Ignoring Cancel: A Cancel response should not accidentally be written into a cell or passed to a conversion function. Handle it before processing the string.
  • Using an ambiguous prompt: “Enter a date” does not tell users whether to enter month-first or day-first. State the required format in the prompt.
  • Assuming macros work everywhere: VBA runs in desktop Excel, not Excel for the web. Mac Excel supports VBA, but Windows-only controls and APIs may not be portable.

Frequently Asked Questions

Can VBA InputBox show a date picker?
No. The standard VBA InputBox is a text-entry dialog, not a calendar control. A custom UserForm or a worksheet-based date entry process is an alternative, but check compatibility before relying on third-party or ActiveX controls.

How do I check whether an InputBox response is a date?
For a local-format response, use IsDate before converting with CDate. For predictable results across regions, require a fixed format such as YYYY-MM-DD, validate its structure, and check that the resulting calendar date matches the entered components.

How do I tell whether the user clicked Cancel?
With Application.InputBox, Cancel returns Boolean False. Store the result in a Variant and check VarType before treating it as text. The VBA InputBox function instead returns an empty string on Cancel, which is hard to distinguish from a deliberately blank response.

Why does the same date text mean different dates on two computers?
Date strings can be parsed using each computer’s regional settings. A slash-form entry such as 04/05/2026 is ambiguous. Specify the expected order or use an unambiguous, fixed input format and parse it explicitly.

Can I use a VBA date InputBox in Excel for the web?
No. Excel for the web does not run VBA macros. Use worksheet cells and features such as Data Validation for an entry workflow that needs to work in the browser, or use desktop Excel to run the macro.

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.