To format a date in an Excel cell with VBA, set the cell’s NumberFormat property, for example: Range("A1").NumberFormat = "yyyy-mm-dd". This changes how Excel displays the value, not the underlying date; use a real date value such as DateSerial(2026, 11, 3) when you also need to enter or replace the cell’s contents.
The basic VBA date-formatting method
Excel stores a valid date as a number and applies a number format to show that number as a date. In VBA, the standard property for setting an Excel cell’s display format is Range.NumberFormat. The format string uses Excel’s number-format syntax, so common patterns include "m/d/yyyy", "dd-mmm-yyyy", and "yyyy-mm-dd".
This short macro formats the value already in cell A1:
Sub FormatOneDateCell()
' Format the display without changing the cell value.
Worksheets("Sheet1").Range("A1").NumberFormat = "yyyy-mm-dd"
End Sub
For a column of dates, assign the format to the relevant range. If column B contains dates and a header, for example, formatting B2:B100 avoids applying a date style to the header:
Sub FormatDateColumn()
Dim ws As Worksheet
' Set the worksheet and format its date cells.
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("B2:B100").NumberFormat = "dd-mmm-yyyy"
End Sub
Before running a macro, save a copy of the workbook. Macro actions generally cannot be reversed with Ctrl+Z, so a backup is the simplest way to protect the original values and formatting.
Formatting a date does not change its value
A date format controls appearance; it does not turn text into a date or alter the stored date. If A1 contains November 3, 2026, applying "mmm d, yyyy" makes it appear as “Nov 3, 2026.” Applying "yyyy-mm-dd" makes the same value appear as “2026-11-03.” Excel can still use that underlying value in date calculations, sorting, and date-based filters.
This distinction explains a common surprise: changing a cell’s format may appear to do nothing when its contents are text, such as the literal text “11/3/2026.” A number format changes numeric values and dates, but it does not parse arbitrary text into a date. To create a date in VBA, assign a Date value, for example:
Sub WriteAndFormatDate()
Dim ws As Worksheet
' Use DateSerial to construct an unambiguous date.
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = DateSerial(2026, 11, 3)
ws.Range("A1").NumberFormat = "yyyy-mm-dd"
End Sub
DateSerial(year, month, day) takes its parts in a fixed order, avoiding the ambiguity of strings such as “11/3/2026,” which can be interpreted differently depending on regional date conventions.
Choosing an Excel date format
Pick a format that suits the people reading the workbook and the way dates will be used. A numeric format is compact, while a month name can make a date easier to interpret across audiences. For dates shared internationally, a year-first format helps avoid confusion between day-first and month-first conventions.
"m/d/yyyy"displays a date such as 11/3/2026 in month/day/year order."dd/mm/yyyy"displays it as 03/11/2026 in day/month/year order."dd-mmm-yyyy"displays it as 03-Nov-2026, with the month written as text."mmmm d, yyyy"displays it as November 3, 2026."yyyy-mm-dd"displays it as 2026-11-03, a clear year-first style often used in reports and file-oriented workflows."m/d/yyyy h:mm AM/PM"displays both the date and time when the value includes a time component.
Excel may show month names or separators according to regional settings, even when a format is set through VBA. If a workbook will travel between regions, test its display in the destination environment and consider a year-first numeric format where ambiguity matters.
Format a whole range or set a default
Use the smallest sensible range when applying a date format. Formatting an entire column is convenient, but it can affect cells used for other purposes and can make later review harder. A bounded range is often preferable when the data size is known. If rows are added regularly, a table column or a range that the macro calculates from the last populated row may be more maintainable.
To format all of column B, the assignment can target Range("B:B"). For a workbook that has a header in row 1 and data down to the last used cell in B, a macro can find that last row and format only the data cells:
Sub FormatUsedDateCells()
Dim ws As Worksheet
Dim lastRow As Long
' Find the last non-empty cell in column B.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
If lastRow >= 2 Then
ws.Range("B2:B" & lastRow).NumberFormat = "dd-mmm-yyyy"
End If
End Sub
This example assumes the date list is contiguous enough that the last non-empty cell in column B marks the end of the data. If the column contains gaps or unrelated notes below the list, choose a more reliable boundary, such as the table’s data body range, rather than relying on the last used cell.
NumberFormat versus VBA Format
These similarly named features do different jobs. Range.NumberFormat applies an Excel display format to a cell or range. VBA’s Format function returns a formatted string. Use the property when the worksheet should retain a usable date value; use the function when code needs to produce text for a message, label, or export.
For instance, Format(DateSerial(2026, 11, 3), "mmmm d, yyyy") returns text that reads “November 3, 2026.” If that text is written to a worksheet, it is text rather than an Excel date serial. It may not behave like a date in calculations or sorting. For a cell that should remain a real date, write the date value and set NumberFormat separately.
There is also a related property called NumberFormatLocal. It reads or sets a format using the language and regional settings of the Excel installation. That can be useful when matching a local format string, but it may make a macro less portable if the workbook is used on computers with different locales. For general VBA workbooks, NumberFormat is usually the more portable starting point.
Locale, separators, and month-minute codes
Excel’s number-format codes include tokens for year, month, day, hour, minute, and second. In date formats, yyyy is a four-digit year, dd is a two-digit day, and mmm is an abbreviated month name. The letter m can represent either a month or minutes, depending on context. In a date-and-time pattern such as "h:mm", it represents minutes; near a day or year token, it represents the month.
Do not assume that the slash in "m/d/yyyy" will always appear as a literal slash on every computer. Excel can use regional conventions when displaying date separators. If exact character output is essential, test the workbook on the relevant regional settings; if the result must be literal text, create text deliberately and accept that it is no longer a date value. That tradeoff matters for later calculations and sorting.
On Microsoft 365 for Windows, the VBA editor can be opened with Alt+F11. On a Mac, the editor shortcut may depend on the keyboard and function-key settings; use the Developer tab or Excel’s Tools > Macro menu to open the Visual Basic Editor if the shortcut differs. VBA macros run in the desktop Excel application, not in Excel for the web. Macros that format a range using NumberFormat are generally the same across desktop platforms, though other workbook features or code dependencies can vary.
Common problems and how to diagnose them
If a date appears as a large number after a macro runs, the cell may simply have a General or numeric format. Apply an explicit date format with NumberFormat. Excel’s stored date serial is a numeric value, so an unformatted date can look like an ordinary number rather than a calendar date.
If formatting has no visible effect, check whether the cell contains text. A quick manual check is to select the cell and inspect the formula bar and alignment or try a date calculation in a spare cell. Text dates may need to be parsed and converted before a date number format can display them as dates. Avoid blindly applying CDate to ambiguous date strings: its interpretation can depend on regional settings. When the source parts are known, construct the value with DateSerial.
If the date is one day off, investigate the source value and the workbook’s date system. Excel workbooks can use the 1900 or 1904 date system, and the same serial number does not represent the same calendar date in both systems. This can matter when dates are copied between workbooks. Excel also retains a historical compatibility behavior in its 1900 date system concerning the nonexistent date February 29, 1900; most everyday date entry does not require special handling, but serial-date conversions and imported data deserve care.
If a cell shows a date but later calculations include unexpected hours, the value may include a time fraction. A date-time value stores the date and time together; a date-only number format merely hides the time portion. To remove the time, change the underlying value intentionally, for example by assigning a date-only value after confirming that losing the time is appropriate. Do not assume that formatting alone cleans or rounds the data.
Applying the format safely in a macro
Before running a formatting macro, confirm the worksheet name and the target range. A typo in a range reference can send a correct format to the wrong cells, while a broad range can overwrite formats that were deliberately set for other data. Save a separate copy first, especially when a macro also changes values, clears cells, or processes imported data.
To run a macro in desktop Excel for Microsoft 365 on Windows, save the workbook in a macro-enabled format such as .xlsm if you need to retain its VBA code, enable macros only when you trust the file, then run the procedure from the VBA editor or the Macro dialog. The Developer tab may need to be enabled before it appears on the ribbon. On Mac, the workbook must likewise be opened in desktop Excel with macros permitted. Excel for the web can open many macro-enabled workbooks, but it does not run VBA macros.
Formatting is usually reversible by setting another number format, but running a macro is not reliably reversible through Excel’s Undo history. A saved copy is therefore more useful than relying on Ctrl+Z. If the macro is intended for repeated use, make it target a clearly defined table or named range, and comment the assumptions about headers and date data.
Frequently Asked Questions
How do I change the date format in VBA?
Set the range’s NumberFormat property, such as Range("A1").NumberFormat = "dd-mmm-yyyy". This changes the display format without changing a valid date value.
How do I enter a date with VBA?
Assign a date value separately from its display format. DateSerial(2026, 11, 3) creates November 3, 2026 using explicit year, month, and day arguments.
Why does my date show as a number?
Excel stores dates as numbers. If the cell is showing the serial value, apply a date format such as "m/d/yyyy" through NumberFormat.
Does NumberFormat convert text into a date?
No. It formats numeric values, including real Excel dates, but does not reliably convert text. Convert the source to a genuine date value first, taking care with locale-dependent strings.
What is the difference between NumberFormat and Format?NumberFormat changes how a worksheet cell displays its value. VBA’s Format function returns formatted text, which is useful for messages but is not itself an Excel date value.
Related
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
- Excel Split Cell Text Into Separate Cells: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Blocked? How to Unblock It SafelyIf Excel says a macro is blocked, first check where the workbook came from; Microsoft 365 for Windows…
- VBA Find: Search Excel Ranges and Handle Results SafelyIn Excel VBA, Find is a method of a Range, not a separate object: it searches a specified…
- VBA MsgBox Return Values and Button ConstantsIn VBA, MsgBox returns a number identifying the button the user clicked: vbOK is 1, vbCancel is 2,…
- Excel VBA Find Position in a String with InStr ExamplesIn Excel VBA, use InStr to find the position of one string inside another: InStr(1, textValue, searchText, vbTextCompare)…
- VBA Create New Workbook With Name: A Practical GuideIn Excel VBA, create a workbook with Workbooks.Add, then give it a lasting name and location with SaveAs.…
- VBA Split Function: Syntax, Examples, and Common UsesIn VBA, Split divides a text string wherever a chosen delimiter appears and returns the pieces as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.