To format a range as dates with Excel VBA, assign a date format to its NumberFormat property, for example Worksheets("Data").Range("B2:B100").NumberFormat = "dd-mmm-yyyy". This changes how numeric date values appear; it does not convert text that merely looks like a date into real Excel dates.
Format a range with VBA
For a repeatable task, put the formatting instruction in a macro and identify both the worksheet and the range explicitly. The example below formats cells B2 through B100 on a sheet named Data:
Sub FormatDateRange()
Dim ws As Worksheet
Dim dateRange As Range
' Set the worksheet and the cells to format.
Set ws = ThisWorkbook.Worksheets("Data")
Set dateRange = ws.Range("B2:B100")
' Display date values as day, abbreviated month, and year.
dateRange.NumberFormat = "dd-mmm-yyyy"
End Sub
Change the worksheet name, range address, and format string to match your workbook. For example, use ws.Range("C:C") to apply a format to an entire column, or ws.Range("A2:D50") to format a rectangular area. Formatting an entire column is valid, but limiting the range to the rows that contain data can make a workbook easier to manage.
Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if the macro targets the wrong cells.
What NumberFormat changes
Excel stores ordinary dates as serial numbers and displays them according to a cell’s number format. The value represents a date (and possibly a time); the format controls its appearance. Setting NumberFormat changes that appearance without replacing the underlying value. A cell containing the same date can therefore show as 3/7/2026, 07-Mar-2026, or 2026-03-07 while retaining the same stored date.
This distinction matters when sorting, filtering, calculating elapsed days, or using dates in formulas. A genuine Excel date can normally participate in those operations as a number. Text such as "07-Mar-2026", however, may remain text even when a date number format is applied. The format does not parse or repair the text.
Excel dates may also include a time, stored as a fractional part of a day. A date-only format hides the time portion but does not remove it. If a value is 7 March 2026 at 6:00 a.m., formatting it as dd-mmm-yyyy displays only 07-Mar-2026; calculations can still be affected by the hidden time.
Choose a date format string
The format string determines which date parts are displayed and how they appear. Common choices include:
"m/d/yyyy"displays a numeric month and day with a four-digit year, such as 3/7/2026."dd/mm/yyyy"displays day before month, such as 07/03/2026. Confirm that this order is suitable for the people who will read the workbook."dd-mmm-yyyy"displays an abbreviated month name, such as 07-Mar-2026, which can reduce day/month ambiguity."mmmm d, yyyy"displays a full month name, such as March 7, 2026."yyyy-mm-dd"displays an unambiguous year-month-day sequence, such as 2026-03-07.
In a custom Excel format, d represents the day, m the month in a date context, and y the year. One or two letters generally give a numeric day or month; three or four can show abbreviated or full names. Excel interprets a format code in context, so m can mean minutes in a time format. For a date-only range, use a clear date pattern rather than mixing date and time codes without checking the result.
Format only the intended cells
A macro should point to the correct workbook, worksheet, and cells. In the example, ThisWorkbook means the workbook containing the VBA code. That is often safer than relying on ActiveWorkbook, which can refer to a different workbook if another file is active when the macro runs. Qualifying the worksheet also avoids depending on whichever sheet is selected.
If the range is based on the current selection, you can use Selection.NumberFormat = "dd-mmm-yyyy", but this is less controlled: the selection might be a chart, an unrelated range, or the wrong cells. For a reusable macro, a named range or an explicitly specified address is usually clearer. If the data grows over time, consider using an Excel table column or calculating the last used row rather than leaving a fixed endpoint that may exclude new records.
To format a noncontiguous set of cells, VBA can use a union of ranges, but make sure every area contains date values before applying the same format. Applying a date display to text, identifiers, or unrelated numeric data can make those values look confusing without changing what they mean.
NumberFormat and NumberFormatLocal
For most VBA macros, use Range.NumberFormat. It accepts Excel’s number-format code and is the usual choice for a format string stored in VBA. Range.NumberFormatLocal instead uses the format-code conventions of the user’s local Excel language and regional settings. It can be useful when a macro must work with a localized format code, but code written for one locale may not behave as expected in another.
Regional settings can affect how a date is conventionally written and how separators appear. A date such as 4/5/2026 can be read as April 5 or May 4 depending on the intended order. An abbreviated month format or an ISO-style display such as yyyy-mm-dd can make the displayed result clearer. When a workbook will be shared internationally, choose a display pattern that minimizes ambiguity and verify it on the target Excel installation.
Why a formatted cell may not look like a date
If a macro runs but the cell still does not behave as a date, check the cell’s value as well as its display format. A number format cannot turn text into a serial date. Text imported from a CSV, a web page, or another system may contain leading apostrophes, spaces, or a date order Excel cannot interpret. Convert or parse the data first, then apply the desired display format.
Other common checks include:
- Wrong target: Confirm the sheet name and range address. A macro can run successfully while formatting a different range than the one being inspected.
- Text values: Test whether the cells contain real numeric dates rather than text. A text value can remain left-aligned by default, although alignment alone is not a definitive test.
- Hidden time: A date-only display can conceal a time fraction. If time matters, use a format such as
"dd-mmm-yyyy h:mm"to show it. - Column width: Widen the column if the displayed date is replaced by hash marks. The underlying value is not necessarily invalid.
- Formula results: A formula can return a date serial, text, or an error. Formatting affects numeric date results, but it will not make a text result a date.
For text conversion, choose a method that matches the source data and its regional date order. Excel’s Text to Columns wizard can convert some imported date strings when the correct date order is selected, but blindly converting ambiguous values can silently produce the wrong dates. Keep an untouched source copy until the converted results have been checked.
Run the macro and preserve the workbook
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the macro into it. Run it with the cursor inside the procedure by pressing F5, or return to Excel and choose Developer > Macros, select the macro, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.
On a Mac, use Developer > Visual Basic if the Developer tab is available. The Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. Excel for the web does not run VBA macros, so open the workbook in the desktop Excel application to execute this code. Menu and shortcut availability can vary with keyboard settings.
If the workbook needs to retain the macro, save it as an Excel Macro-Enabled Workbook (.xlsm), not as a macro-free .xlsx file. Only enable macros in files you trust. A formatting macro can alter many cells quickly, and VBA changes cannot be undone with Ctrl+Z; save a separate copy before execution and verify the target range first.
Frequently Asked Questions
How do I format an entire column as a date in VBA?
Use a qualified column range, such as ws.Columns("B").NumberFormat = "dd-mmm-yyyy", after setting ws to the intended worksheet. This applies the display format to the whole column, including currently blank cells.
Does NumberFormat convert text into dates?
No. It changes the display format of values Excel can treat as numbers; it does not convert text strings into date serials. Convert the text to genuine dates first, taking care to interpret the source’s day and month order correctly.
Can I show a date as yyyy-mm-dd?
Yes. Set the range’s NumberFormat to "yyyy-mm-dd". For example, a genuine date for 7 March 2026 will display as 2026-03-07.
Why does my date display as a number?
The cell may have General or another numeric format, or the value may be a date serial. Apply a date format to reveal the date-style display. If the value is text, first convert it to an actual date.
Can I undo a VBA date-formatting macro?
Excel does not provide the normal Ctrl+Z undo history for VBA changes. Restore a saved copy or run another macro that applies the previous format. Save a backup before running code that changes a range.
Will the format look identical on every computer?
Not always. Regional settings and localized Excel conventions can affect date presentation. Use an unambiguous format, check the workbook on the intended systems, and use NumberFormatLocal only when local format-code behavior is specifically needed.
Related
- Excel Table Data Filtering: How to Narrow Rows
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Import CSV to Excel Using VBA: A Practical GuideTo import a CSV file into Excel with VBA, use Workbooks.OpenText to parse the file, then copy its…
- Excel VBA Dictionary Keys: Add, Find, and Manage ItemsIn Excel VBA, a dictionary key is a unique identifier used to store and retrieve a corresponding value.…
- VBA Code to Link Excel WorksheetsTo link to a worksheet in the same Excel workbook, use VBA’s Hyperlinks.Add method with an empty Address…
- VBA Dictionary Split: Turn Text into Keys and ValuesIn VBA, use the Split function to break a string into an array, then add the pieces to…
- Macro Recorder for Games: How It Works and What to KnowA macro recorder for games captures a sequence of keyboard or mouse inputs and replays them, often with…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a specified range and return the first matching cell;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.