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 as2025-03-08."m/d/yy"displays a short numeric date, such as3/8/25."dd mmmm yyyy"displays a day, full month name, and year, such as08 March 2025."ddd, dd mmm yyyy"displays abbreviated weekday and month names, such asSat, 08 Mar 2025."hh:nn AM/PM"displays hours and minutes with a 12-hour clock, such as09: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, usennfor minutes. Usemmfor a month. - Expecting a format to change the stored value:
Format$returns text.NumberFormatchanges 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’sNumberFormatuse related but not identical conventions, notably for minutes. - Expecting the number format to repair data: Applying
yyyy-mm-ddto 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
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
- Excel Spreadsheet Grouping Rows: How to Create and Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Get the Number of Sheets in a Workbook EasilyTo get the number of sheets in a workbook with VBA, use the workbook’s Sheets.Count property—for example, ThisWorkbook.Sheets.Count.…
- Excel VBA GoTo: Syntax, Examples, and Error HandlingIn Excel VBA, GoTo transfers execution to a named label elsewhere in the same procedure. It is most…
- VBA Sort Dictionary Keys: Methods and ExamplesTo sort VBA dictionary keys, copy them into the array returned by Dictionary.Keys, sort that array, and loop…
- UserForm VBA Properties: What They Do and How to Set ThemUserForm VBA properties control a form’s identity, appearance, size, position, and behavior in an Excel desktop workbook. You…
- VBA Find Position of a Substring in a StringIn VBA, use InStr to find a substring’s position within a string: it returns the position of the…
- VBA InputBox New Line: Add Line Breaks to PromptsTo put a new line in a VBA InputBox prompt, join the prompt text with vbCrLf or vbNewLine.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.