Excel VBA Date String Formatting Explained

VBA & macros · Updated November 21, 2026

In Excel VBA, use Format$ to turn a date value into a formatted text string, such as Format$(Date, "yyyy-mm-dd"). To change how a date looks in a worksheet without converting it to text, set the cell’s NumberFormat property instead; choosing between these two approaches is the key to getting the result you expect.

Formatting a date versus formatting a cell

A date in an Excel cell is normally stored as a number, with a date number format controlling how that number appears. VBA’s Format$ function works differently: it returns a string. For example, formatting a date as "dd mmmm yyyy" might return "08 March 2025". That result is text, not a date value that Excel can sort, subtract, or use in date calculations in the same way.

Use Format$ when you need date characters for a message, a filename, a report heading, or an export. Use Range.NumberFormat when you want a worksheet cell to keep its underlying date value and only change its display. This distinction also affects later operations: text that looks like a date may be interpreted differently on another computer, while a date value remains usable for date arithmetic.

The dollar sign in Format$ indicates a string-returning version of the VBA function. Format also works in VBA, but Format$ makes the intended string result clear. Neither function changes the value in a worksheet cell unless you assign its result to that cell.

How VBA date formats work

The general pattern is Format$(expression, format_string). The expression can be a VBA Date variable, a date returned by a function such as Date or Now, or a value that VBA can interpret as a date. The format string specifies the pieces to display. For instance, a date value representing March 8, 2025 formatted with "m/d/yyyy" produces "3/8/2025" in a typical U.S. setup.

Declare variables with a suitable type so the code communicates what it expects. A Date variable holds a date/time value; a String variable holds the formatted characters. This small distinction helps prevent accidental conversions later.

Sub ShowFormattedDate()
    Dim d As Date
    Dim dateText As String

    ' Use an unambiguous date constructor.
    d = DateSerial(2025, 3, 8)
    dateText = Format$(d, "yyyy-mm-dd")

    Debug.Print dateText  ' Prints: 2025-03-08
End Sub

DateSerial(year, month, day) constructs a date from numeric parts and avoids relying on whether a typed date such as "3/8/2025" means March 8 or August 3 on a particular system. In the example, the result is the text 2025-03-08. The code prints it in the Immediate window; it does not alter a worksheet cell.

Useful date and time format codes

VBA format patterns use letters to represent date and time components. Repeating a letter usually changes the width or detail. The surrounding text is not automatically added: include separators and spaces where you want them. These patterns are useful starting points:

  • "yyyy-mm-dd" displays a four-digit year, month, and day, such as 2025-03-08.
  • "m/d/yy" displays a short numeric date, such as 3/8/25.
  • "dd mmmm yyyy" displays a day, full month name, and year, such as 08 March 2025.
  • "ddd, dd mmm yyyy" displays abbreviated weekday and month names, such as Sat, 08 Mar 2025.
  • "hh:nn AM/PM" displays hours and minutes with a 12-hour clock, such as 09:45 AM.
  • "yyyy-mm-dd hh:nn:ss" displays date and time down to seconds.

A particularly important VBA detail is the minute code: use n or nn for minutes in a VBA Format$ date/time pattern. In this context, m or mm represents the month. A pattern such as "hh:mm" can therefore give a month where minutes were intended. In Excel’s worksheet number formats, by contrast, m is commonly used for minutes when the context makes that meaning clear, such as next to hours or seconds.

Month and weekday names can follow the computer’s regional language settings. If you need a fixed language or a strict interchange format, test the output on the systems that will use it. A numeric pattern such as yyyy-mm-dd avoids localized month names, but date parsing and regional settings still matter when text is later read back in.

Change a worksheet date’s display

To keep cells as dates, assign a format to their NumberFormat property. The following macro changes the display of cells A2:A100 on a worksheet named Sheet1; it does not convert date values into text.

Sub FormatDateColumn()
    Dim ws As Worksheet
    Dim dateCells As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set dateCells = ws.Range("A2:A100")

    ' Keep the values as dates and change only their display.
    dateCells.NumberFormat = "yyyy-mm-dd"
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. If the worksheet has a different name or your dates occupy a different range, edit those references before running the example. The format affects date numbers in the selected range; ordinary text entries will remain text.

In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure there. On Mac, the Visual Basic Editor shortcut is generally Option+F11 (on some keyboards, include the Fn key); you can also open it from the Developer tab. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop application for this code.

Create a formatted date string for output

When you need text rather than a date-valued cell, assign the result of Format$ to a string variable. You can then write that string to a cell, include it in a message, or use it as part of a filename. For example, after declaring d as a Date and setting it to March 8, 2025, Format$(d, "dd-mmm-yyyy") returns "08-Mar-2025" in an English-language regional setting.

To place the string in a worksheet, assign it explicitly. With ws set to the worksheet and d set to the date, the statement ws.Range("B2").Value = Format$(d, "yyyy-mm-dd") writes text to B2. If you want B2 to remain a date value, instead write d to the cell and set B2’s NumberFormat. Do not apply both approaches without a reason: the first is text, while the second is a date value displayed with a pattern.

Formatted strings are handy for labels such as "Report generated: " & Format$(Now, "yyyy-mm-dd hh:nn"). They are less suitable as the only stored representation of dates in a data table. For reliable sorting, filtering, and calculations, keep a real date in the data and format it for display. A separate text label can be generated when a report or export specifically requires one.

Reading date strings safely

Formatting and parsing are separate tasks. Format$ creates text; it does not make an arbitrary text string safe to convert back into a date. Functions such as CDate interpret text according to regional settings, so a string like "03/08/2025" can be ambiguous. It may represent March 8 or August 3 depending on the expected date order.

If a source supplies dates in a known year-month-day order, split the parts and construct the date explicitly rather than relying on an ambiguous conversion. This example handles a string with exactly three hyphen-separated parts, such as 2025-03-08:

Sub ParseIsoDateText()
    Dim dateText As String
    Dim parts() As String
    Dim parsedDate As Date

    dateText = "2025-03-08"
    parts = Split(dateText, "-")

    If UBound(parts) = 2 Then
        ' The known order is year, month, day.
        parsedDate = DateSerial(CInt(parts(0)), _
                                CInt(parts(1)), _
                                CInt(parts(2)))
        Debug.Print Format$(parsedDate, "yyyy-mm-dd")
    Else
        Debug.Print "Expected yyyy-mm-dd"
    End If
End Sub

This illustrates the basic idea, not complete validation for untrusted input: production code should also check that each part is numeric and that the date is valid. VBA’s DateSerial normalizes out-of-range parts; for example, an excessive day value can roll into a later month. If exact input validation matters, compare the constructed date’s year, month, and day back to the supplied parts and reject mismatches. Also account for blank cells, error values, and non-date text before converting worksheet data.

Common mistakes and practical checks

  • Using the wrong minute symbol: In VBA’s Format$ pattern, use nn for minutes. Use mm for a month.
  • Expecting a format to change the stored value: Format$ returns text. NumberFormat changes how a numeric date is displayed.
  • Assuming a slash means the same thing everywhere: Numeric date strings and slash-based formats can be interpreted according to regional settings. Prefer an explicitly ordered representation when exchanging text.
  • Formatting a value that is not a date: A cell may contain text that merely resembles a date, or a blank or error. Check the input before converting it.
  • Confusing VBA and Excel format codes: VBA’s Format$ and a worksheet cell’s NumberFormat use related but not identical conventions, notably for minutes.
  • Expecting the number format to repair data: Applying yyyy-mm-dd to text does not turn that text into a date. Convert valid text to a date value first if calculations are required.

Excel dates are stored as serial values, with the displayed date determined by a number format. Workbooks can use different date systems, so copying raw serial numbers between workbooks is not always equivalent to copying actual date values. For ordinary VBA work, assign and handle date values as dates, and use NumberFormat for display instead of manipulating serial numbers directly.

Frequently Asked Questions

How do I format a date as yyyy-mm-dd in Excel VBA?
For text, use Format$(someDate, "yyyy-mm-dd"). For a worksheet cell that should remain a date, set its NumberFormat property to "yyyy-mm-dd".

How do I display the month name in VBA?
Use a pattern such as Format$(someDate, "mmmm") for the full month name or Format$(someDate, "mmm") for an abbreviation. The language of the name can depend on regional settings.

Why does VBA show the month instead of minutes?
In a VBA Format$ date/time pattern, m and mm refer to the month. Use n or nn for minutes, as in "hh:nn".

Does Format$ change the date in the cell?
No. It returns a string. To change a cell’s display while retaining its date value, set Range.NumberFormat; to replace the cell’s value with text, assign the result of Format$.

Can Excel for the web run VBA date-formatting macros?
No. Excel for the web can work with workbooks that contain VBA, but VBA macros must be edited and run in the Excel desktop application. In the browser, use built-in formatting tools or another supported automation option instead.

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.