VBA Format Date String: Examples, Patterns, and Fixes

VBA & macros · Updated November 18, 2026

In VBA, use Format or Format$ to turn a date value into a text string: for example, Format$(Date, "yyyy-mm-dd") produces a year-month-day string. Choose the format pattern to control the output; use nn for minutes in a time pattern, not mm.

What VBA Format Does

Format(expression, format) takes a value and a format pattern, then returns a formatted result. For a date, the expression should be a valid VBA date value, such as a variable declared As Date, the result of Date or Now, or a date safely converted from a cell. The pattern determines whether the result shows a numeric date, a written-out month, a time, or a combination.

Format returns a Variant containing a formatted string. Format$ returns a String directly, so it is often a natural choice when the intention is specifically to create text. Both use the same kind of date and time patterns. Formatting changes how the value is represented; it does not change the underlying date or perform a date calculation.

For example, with the computer’s current date, Format$(Date, "yyyy-mm-dd") returns text such as 2026-11-02. The actual result depends on the date when the code runs. Use a fixed date value when you need a repeatable example or output.

Common Date and Time Pattern Characters

VBA date patterns use letters and other characters to describe the parts to display. These are the most useful date tokens:

  • d displays the day as a number, while dd pads a one-digit day with a zero.
  • ddd displays an abbreviated weekday name, and dddd displays the full weekday name.
  • m displays the month as a number; mm pads it with a zero. mmm displays an abbreviated month name, and mmmm displays the full name.
  • yy displays a two-digit year; yyyy displays a four-digit year.
  • h and hh display the hour, and s and ss display seconds.
  • n and nn display minutes. Using nn makes the intent clear in a time pattern.

A pattern like dddd, mmmm d, yyyy produces a readable date such as Monday, November 2, 2026. A compact pattern like dd-mmm-yy produces 02-Nov-26. For an unambiguous four-digit year and numeric month and day, yyyy-mm-dd is a useful text representation.

Formatting a Date with a Short VBA Example

Declare a date variable and assign a date with DateSerial when you want to avoid interpreting a potentially ambiguous date string. The following example prints three representations of the same date in the Immediate window. In the VBA editor, open that window with Ctrl+G on Windows.

Sub ShowDateFormats()
    Dim dt As Date
    Dim compactText As String
    Dim readableText As String
    Dim weekdayText As String

    ' Build November 2, 2026 without parsing a regional date string.
    dt = DateSerial(2026, 11, 2)

    compactText = Format$(dt, "yyyy-mm-dd")
    readableText = Format$(dt, "mmmm d, yyyy")
    weekdayText = Format$(dt, "dddd")

    Debug.Print compactText
    Debug.Print readableText
    Debug.Print weekdayText
End Sub

The output is 2026-11-02, November 2, 2026, and Monday. The format string controls the result, but the value stored in dt remains a date. Before running a macro that changes workbook data, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

Including Time in the String

Use Now when you need the computer’s current date and time, or combine a date with TimeSerial for a fixed example. In VBA’s date format patterns, n represents minutes. The letter m represents the month in date patterns and can be interpreted as minutes in a time context, but nn avoids an easy-to-miss ambiguity.

Sub ShowDateAndTime()
    Dim dt As Date
    Dim timestampText As String

    ' Create a fixed date and time for a repeatable example.
    dt = DateSerial(2026, 11, 2) + TimeSerial(14, 5, 9)

    timestampText = Format$(dt, "yyyy-mm-dd hh:nn:ss")
    Debug.Print timestampText
End Sub

This prints 2026-11-02 14:05:09 using a 24-hour clock. For a 12-hour clock with an AM or PM marker, use a pattern such as h:nn:ss AM/PM; for example, the same time is shown as 2:05:09 PM. A date-only value such as one made with DateSerial has no meaningful time-of-day component, so including time tokens will show midnight.

If you need a literal separator such as the letter T between the date and time, concatenate separately formatted parts. That avoids confusing a literal character with a format token: Format$(dt, "yyyy-mm-dd") & "T" & Format$(dt, "hh:nn:ss") produces a string such as 2026-11-02T14:05:09.

Locale, Date Separators, and Parsing

Formatting and parsing are different operations. Format$ takes an existing date value and displays it; it does not decide whether an incoming text value like 03/04/2026 means March 4 or April 3. When VBA converts text with a function such as CDate, interpretation can depend on the computer’s regional settings. Avoid ambiguous date text when code must behave consistently on different computers.

For dates assembled from numeric year, month, and day parts, DateSerial(year, month, day) is generally clearer than converting a regional string. For example, DateSerial(2026, 4, 3) explicitly supplies the third day of the fourth month. Month and weekday names produced by patterns such as mmmm and dddd can also reflect the system’s language and regional settings. If another system requires a particular language or a strict interchange format, check the actual output under the target environment rather than assuming names will be English.

VBA also treats slash and colon characters in date/time format strings as date and time separators, which can be affected by regional settings. A hyphen in a pattern such as yyyy-mm-dd is a practical choice when you want a stable hyphen-separated result. Even then, the pattern only formats a valid date; it does not validate user input or make a local-time value timezone-aware. VBA’s Date and Now represent the computer’s local date and time, not a value with an attached timezone.

Returning Text or Formatting an Excel Cell

A frequent source of confusion is the difference between a VBA string and a cell’s display format. Use Format$ when code needs text, such as a date in a message, a filename component, or a text export. Use a cell’s NumberFormat property when the cell should keep a numeric date value but display it in a chosen style. Numeric dates can still be sorted and used in date calculations; a string that looks like a date is text and may not behave the same way.

Sub FormatDateCell()
    Dim target As Range

    ' Change the display format without replacing the cell value.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    target.NumberFormat = "yyyy-mm-dd"
End Sub

This code changes the display format of cell A1 on Sheet1; it does not convert the cell’s contents into a text string. Excel’s NumberFormat property uses Excel number-format codes. These often look like VBA date patterns, but they belong to the worksheet formatting system, not to the VBA Format$ function. If the goal is to put text in another cell, assign the result of Format$ instead, and remember that the destination is then text.

When reading worksheet data, check what is actually in the cell. A blank cell, an error, or arbitrary text may not convert safely to a date. For a cell intended to contain a date, validate before converting:

Sub ReadDateAsText()
    Dim sourceValue As Variant
    Dim dt As Date
    Dim dateText As String

    ' Read the value and reject blanks or non-date input.
    sourceValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
    If IsDate(sourceValue) Then
        dt = CDate(sourceValue)
        dateText = Format$(dt, "yyyy-mm-dd")
        Debug.Print dateText
    Else
        Debug.Print "Cell A1 does not contain a recognizable date."
    End If
End Sub

IsDate checks whether VBA recognizes the value as a date, but text recognition can still be regional. For robust workbook workflows, keep dates as actual date values in cells and avoid relying on ambiguous text as input. Save a copy before running a macro that alters workbook contents; macro actions cannot be reversed with Ctrl+Z.

Choosing a Pattern for the Job

The right pattern depends on who or what will read the result. A person may prefer a full month name, while a filename or data exchange often needs a compact, predictable sequence. Consider these examples:

  • Format$(dt, "mmmm d, yyyy") is readable in an English-language setting, but the month name can be localized.
  • Format$(dt, "dd-mmm-yyyy") is compact and keeps a four-digit year, though the abbreviated month name can vary by locale.
  • Format$(dt, "yyyy-mm-dd") is useful as a numeric-looking text label and sorts in calendar order when all values use the same pattern.
  • Format$(dt, "yyyy-mm-dd hh:nn:ss") includes seconds for a timestamp, but does not identify a timezone.

For a filename, consider whether the output must be safe across operating systems and whether two files created on the same day need different names. A date-only pattern can produce identical date portions throughout that day; including time may help, but it still may not guarantee uniqueness if files are created within the same second. For data exchange, follow the receiving system’s documented requirements rather than assuming that a date-looking string will be parsed identically everywhere.

Common Errors and How to Fix Them

  • Using mm for minutes: In date patterns it means month, and its meaning can depend on context. Prefer nn for minutes, as in hh:nn:ss.
  • Getting an unexpected date from text: A string such as 04/05/2026 can be interpreted differently under different regional settings. Use a genuine date value or build one with DateSerial.
  • Seeing a date in a cell but getting a number in code: Excel stores dates as numeric values with a display format. Check the cell value and use a date-aware conversion only when appropriate; changing display formatting and creating text are separate tasks.
  • Expecting a string to remain a date: The result of Format$ is text. Keep the original date value if you still need to sort, compare, or calculate with it.
  • Getting localized month or weekday names: Names can depend on regional settings. Use a numeric pattern when localized words are not acceptable, or handle localization explicitly in the application.

Frequently Asked Questions

How do I format a VBA date as yyyy-mm-dd?
With a valid date variable named dt, use Format$(dt, "yyyy-mm-dd"). It returns text in year-month-day order, for example 2026-11-02.

What is the difference between Format and Format$ in VBA?
Both apply a format pattern. Format returns a Variant containing the formatted result, while Format$ returns a String. Use Format$ when you specifically need text.

How do I display minutes in a VBA date string?
Use n or nn for minutes. For example, Format$(dt, "hh:nn:ss") displays hours, minutes, and seconds. Do not assume mm means minutes; it is the month token in date patterns.

How do I format an Excel cell as a date using VBA?
Set the cell’s NumberFormat property, for example Range("A1").NumberFormat = "yyyy-mm-dd". This changes how the cell displays its value; it does not create a VBA text string.

Why does my VBA date string change on another computer?
Regional settings can affect date separators and month or weekday names, and text-to-date conversion can interpret ambiguous input differently. Use genuine date values, avoid ambiguous input strings, and test the output where it will be used.

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.