Excel VBA Date Formatting: Examples and Fixes

VBA & macros · Updated November 21, 2026

To format a date in Excel VBA, use Format$ when you need formatted text, or set a cell’s NumberFormat when you want the cell to remain a real, usable Excel date. For example, Format$(Date, "yyyy-mm-dd") returns text such as 2026-11-03, while Range("A2").NumberFormat = "dd-mmm-yyyy" changes how a date in A2 appears without changing its underlying value.

Two ways to format date output

The right VBA method depends on what “output” means in your task. Excel stores dates as numbers and displays them using a number format. VBA can either change that display format or create a separate text string. Those approaches can look similar on screen but behave differently in later calculations, sorting, and exports.

  • Format a worksheet cell: assign a date value to the cell and set its NumberFormat. The value remains a date that Excel can calculate with.
  • Format a string: use Format$ (or Format) to turn a date into text for a message, filename, report label, or text export.

For a column of dates that users may filter, sort, or use in formulas, changing the cell’s display is usually preferable. For a filename or a message such as “Report created on 03 Nov 2026,” formatted text is appropriate. A text result that resembles a date is not itself an Excel date.

How Excel dates and VBA formats work

Excel represents a date as a serial number, with any time stored as a fractional part of that number. A cell containing a date can therefore display as 3-Nov-2026, 11/03/2026, or Tuesday, November 3, 2026 without its stored value changing. The number format controls the presentation; it does not change the date or convert it to a different day.

VBA’s Format$ function instead returns a string. Its format pattern describes the parts to show: for example, dd for a two-digit day, mmm for an abbreviated month name, and yyyy for a four-digit year. A format pattern is not the same thing as a date value. If you assign the returned string to a cell, the cell contains text, even if it looks like a date.

For straightforward date output, common patterns include yyyy-mm-dd, dd-mmm-yyyy, and mmmm d, yyyy. The first is a compact year-month-day representation; the second is readable and avoids confusion between day-first and month-first numeric dates; the third includes the full month name. A pattern’s appearance can still depend on regional settings when it uses localized month or weekday names.

Format a date in a worksheet cell

To keep a worksheet date usable, apply a number format to the cell after placing a genuine date value there. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. The following example assumes the date is already in A2 on Sheet1 and only changes its display.

Sub FormatDateInCell()
    Dim ws As Worksheet

    ' Change the display format; preserve the cell's date value.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("A2").NumberFormat = "dd-mmm-yyyy"
End Sub

The result is a date displayed like 03-Nov-2026 if A2 contains November 3, 2026. Because the macro applies a number format rather than writing text, the cell can still be used in date arithmetic. To apply the format to a range, use a range such as ws.Range("A2:A100").NumberFormat = "dd-mmm-yyyy". This does not convert text entries in that range into dates.

In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and paste the procedure. Run it from the editor or from Excel’s Developer tab. On Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11. The Developer tab may need to be enabled in Excel’s ribbon settings. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).

Return formatted date text with Format$

Use Format$ when a string is the intended result. The dollar-sign version returns a String, which makes the result explicit when building text. This example uses a fixed date so its output is easy to predict; DateSerial creates November 3, 2026 without relying on how a computer interprets a typed date.

Sub ShowFormattedDateText()
    Dim reportDate As Date
    Dim outputText As String

    ' Create a date and format it as text.
    reportDate = DateSerial(2026, 11, 3)
    outputText = Format$(reportDate, "dddd, mmmm d, yyyy")
    MsgBox outputText
End Sub

The message is Tuesday, November 3, 2026 in an English-language locale. The day and month names may be localized on a computer using another regional language. A shorter example, Format$(reportDate, "yyyy-mm-dd"), produces 2026-11-03. Use the returned text for a label or filename, but do not use it in place of a true date if the next step requires date calculations.

Format also formats values, but its return type is a Variant. For code that specifically needs a string, Format$ communicates that purpose more clearly. Neither function changes the original date variable. Store the result in a separate string variable if you need to keep both the date and its formatted representation.

Choose a format pattern for the job

Choose a pattern based on the reader, the destination, and whether the result must be unambiguous. Numeric dates such as 04/05/2026 can mean April 5 or May 4 depending on regional convention. A pattern with a month name is easier for people to read across regions, while a year-first numeric form is often convenient for sorting text chronologically.

  • "dd-mmm-yyyy" displays a date like 03-Nov-2026.
  • "mmmm d, yyyy" displays a date like November 3, 2026.
  • "yyyy-mm-dd" displays a date like 2026-11-03 and avoids month/day order ambiguity.
  • "h:mm AM/PM" displays a time like 2:30 PM.
  • "yyyy-mm-dd h:mm" includes both date and time, such as 2026-11-03 14:30.

In VBA date/time patterns, m represents a month, while n represents minutes. That distinction matters in time patterns: use h:nn for hour and minute output in a VBA Format$ pattern, rather than assuming every format token follows Excel’s worksheet custom-format rules. For worksheet cells, use Excel’s number-format syntax and check the displayed result in Excel; for example, NumberFormat = "h:mm AM/PM" is a worksheet-cell format.

For dates written to files or systems that expect a fixed representation, use a deliberate, predictable pattern and consider whether the destination expects a real date field or plain text. A formatted string does not carry the original date’s numeric value, time-zone information, or regional interpretation. If another application will parse the text, follow that application’s documented input format rather than assuming it will interpret a date string the same way Excel does.

Common problems and reliable fixes

A frequent surprise is that a cell shows a number after a macro writes a date. This usually means the value is present but the cell is using a General or numeric format. Apply an appropriate NumberFormat; do not multiply or divide the value just to make it look like a date. Conversely, if changing NumberFormat has no effect, the cell may contain text rather than an Excel date.

Another source of errors is parsing ambiguous date text. A string such as "03/11/2026" may be interpreted differently depending on regional settings. Avoid relying on CDate to resolve an ambiguous string. If the year, month, and day are known as separate numbers, construct the value with DateSerial(year, month, day). Validate data imported from files before formatting it, since formatting cannot repair an invalid or wrongly interpreted date.

  • Unexpected month or minute: in VBA’s Format$ patterns, use m for month and n for minutes.
  • Wrong-looking date order: choose a format with a month name or year first instead of an ambiguous two-number pattern.
  • Text does not sort chronologically: keep actual dates in the worksheet and apply a display format; text sorting follows characters, not date values.
  • Time disappears: the cell may contain a time fraction but use a date-only format. Apply a format that includes hours and minutes.
  • Blank or invalid input causes an error: check the source value before converting it or passing it to a date-formatting routine.

Excel workbooks can use either the 1900 or 1904 date system. The same date can have a different underlying serial number between workbooks, so avoid copying raw serial values between workbooks as though they were universal date identifiers. Usually, copy the actual date value through Excel and confirm the destination workbook’s display. The NumberFormat property affects appearance; it does not fix a date-system mismatch.

Formatting dates in messages, filenames, and exports

For a message box, combine a label with formatted text, for example MsgBox "Run date: " & Format$(Date, "dd-mmm-yyyy"). For a filename, avoid characters that Windows does not allow in file names, such as slashes and colons. A pattern like yyyymmdd produces a compact date such as 20261103, which is also easy to sort as text when every date uses the same pattern.

For a CSV or other text output, decide whether the receiving system expects a formatted string or an actual date field. CSV files store text and values, not Excel cell formatting, so a worksheet’s NumberFormat setting is not a dependable way to control the date text in every export workflow. If your VBA code builds a line of text, apply Format$ to the date as you construct that line. Keep a copy of important source data and check the exported file in a plain-text editor or the intended receiving application.

Frequently Asked Questions

How do I display a date as dd/mm/yyyy in VBA?
For a worksheet cell, set its NumberFormat to "dd/mm/yyyy". For a text string, use Format$(someDate, "dd/mm/yyyy"). The displayed order is explicit, but the slash-separated text can still be misread by software that applies different regional rules.

Does Format$ change the date in the cell?
No. Format$ returns formatted text and does not change the source date. To change how an existing date appears in a cell while preserving it as a date, set that cell’s NumberFormat property.

Why does VBA show a number instead of a date?
Excel stores dates as serial numbers, and a cell using General formatting may display the serial. Apply a date NumberFormat. If the cell contains text that merely resembles a date, convert or re-import it as a valid date before relying on date formatting.

How can I format a date as YYYY-MM-DD?
For text, use Format$(someDate, "yyyy-mm-dd"). For an Excel cell, set NumberFormat to "yyyy-mm-dd". The text result is a string; the cell-format approach retains the numeric date value.

What is the difference between NumberFormat and NumberFormatLocal?
NumberFormat uses Excel’s format-code convention, while NumberFormatLocal uses the format-code language of the user’s local Excel installation. For VBA intended to run across differently localized installations, NumberFormat is generally the more suitable property; test any specialized format on the target setup.

Which method should you use?

Use NumberFormat when the output belongs in a worksheet and must remain a date. Use Format$ when the output must be text, such as a filename, message, or explicitly constructed export field. The key question is not simply how the date should look, but whether the next user or application needs a real date value or a string. Choosing that first prevents many common formatting, sorting, and regional-interpretation problems.

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.