To display Excel dates as dd/mm/yyyy with VBA, set the range’s NumberFormat property, for example: Range("B2:B100").NumberFormat = "dd/mm/yyyy". This changes how valid date values appear; it does not convert the cells into text or change the dates themselves.
The basic VBA date-format statement
In Excel for Microsoft 365 on Windows, NumberFormat applies a number format to a cell or range. The format code uses two digits for the day, two for the month, and four for the year. Put the statement in a VBA procedure and run it on the intended cells.
For example, the following macro formats dates in cells B2 through B100 on a worksheet named Data. Change the worksheet name and range to match your workbook. The macro applies the display format to every cell in the range, including blank cells; blank cells remain blank.
Sub FormatDatesAsDayMonthYear()
Dim ws As Worksheet
' Select the worksheet whose date cells should be formatted.
Set ws = ThisWorkbook.Worksheets("Data")
' Display dates with day, month, and four-digit year.
ws.Range("B2:B100").NumberFormat = "dd/mm/yyyy"
End Sub
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so check the worksheet name and target range before you run the procedure.
What the format changes—and what it does not
Excel stores most dates as numeric serial values and uses a cell’s number format to display those values as dates. Applying dd/mm/yyyy changes the presentation, not the stored value. A cell containing a genuine date for 8 March 2025 will display as 08/03/2025; Excel can still use that value in date calculations, sorting, filtering, and charts.
This distinction matters when a date looks wrong. If a cell contains text such as "08/03/2025", changing its number format usually will not turn it into a usable date. A number format controls how numeric values appear; it is not a general text-conversion tool. Confirm that the source cells contain real Excel dates before assuming the format code has failed.
Formatting also does not determine what an ambiguous text date means. Depending on regional settings, text such as 03/08/2025 could be interpreted as 3 August or March 8. When constructing a date in VBA, use DateSerial rather than relying on a date string with an unclear order.
Apply the format to a different range or column
Replace the example address with the cells that contain your dates. A fixed range is straightforward when the data size is known. If new rows are added regularly, you can find the last used row in the date column and format from row 2 down. The example below assumes row 1 is a heading and column B contains the dates.
Sub FormatUsedDatesInColumnB()
Dim ws As Worksheet
Dim lastRow As Long
' Set the worksheet and find the last nonblank cell in column B.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
' Format data rows only; leave the heading untouched.
If lastRow >= 2 Then
ws.Range("B2:B" & lastRow).NumberFormat = "dd/mm/yyyy"
End If
End Sub
If column B might contain notes or other values below the date list, this last-row method may include those cells. That is usually harmless for display, but a defined table column or a known data range can be more precise. To format a whole column, use ws.Columns("B").NumberFormat = "dd/mm/yyyy"; this affects the entire column, including cells added later, but can be unnecessary work in a large workbook.
NumberFormat and NumberFormatLocal
For a macro intended to run across different language settings, NumberFormat is generally the better choice. It uses Excel’s VBA format-code syntax, such as dd/mm/yyyy. NumberFormatLocal instead uses the format-code language and conventions of the user’s Excel installation. That can be useful when a macro deliberately follows a particular localized display, but it can make format strings dependent on the machine running the code.
The slash in an Excel date format can act as the system date separator. Depending on Windows or Excel regional settings, the displayed separator may therefore differ from a literal slash. If the requirement is specifically to show slash characters, use escaped slashes in the format code: dd\/mm\/yyyy. For example, the assignment is Range("B2:B100").NumberFormat = "dd\/mm\/yyyy". Check the result in the target environment, particularly if the workbook will be shared across regions.
Do not confuse the cell’s display format with the date order used to interpret incoming text. A display format can show a real date as day/month/year, but it cannot reliably resolve ambiguous text imported from a file. For imports, specify the source date order during import or convert the data using a method that explicitly identifies day, month, and year.
Make sure the cells contain real dates
To check a suspicious cell manually, select it and look at the formula bar and the Number Format controls on the Home tab. A genuine date is usually stored as a number, although its appearance depends on formatting. As a diagnostic, temporarily apply General format: if the value becomes a number, that is consistent with a numeric date serial. It is not, by itself, proof that the value represents the intended calendar date.
For dates created in VBA, DateSerial avoids ambiguity in a slash-separated date string. The following example writes 8 March 2025 as a date value and then formats the cell. It assumes the active workbook contains a worksheet named Data.
Sub WriteOneDate()
Dim ws As Worksheet
' Build the date from explicit year, month, and day components.
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("B2").Value = DateSerial(2025, 3, 8)
' Show the date in day/month/year order.
ws.Range("B2").NumberFormat = "dd/mm/yyyy"
End Sub
The cell’s underlying value is 8 March 2025, and the displayed result is 08/03/2025. In contrast, a value entered as text may remain text even if it looks identical on screen. If dates came from a CSV or another system, inspect a few cells and verify that sorting places them in chronological order before relying on them for calculations.
Common problems and how to fix them
- The display order still looks wrong: Confirm that the intended range was targeted and that the format code is
dd/mm/yyyy, notmm/dd/yyyy. Also check whether another macro or a table style reapplies formatting afterward. - The cells do not change: The values may be text, or the code may be running on a different workbook or worksheet. Check the sheet reference and test a known date cell.
- The separator is a dot or dash: Excel may be displaying the system’s date separator. Use escaped slash characters if literal slashes are required, then verify the result under the relevant regional settings.
- Dates appear as a row of hash marks: The column may be too narrow to show the formatted value. Widen the column; the hash marks do not necessarily indicate a bad date.
- The day and month seem swapped: Formatting cannot correct a value that was interpreted incorrectly when imported. Check the original source and convert ambiguous text using its known date order.
- The macro does not run: Confirm that macros are permitted for the workbook and that the code is in a standard VBA module or an appropriate workbook object. Do not enable macros in an untrusted file merely to view its contents.
Run the macro safely in Excel
On Windows, open the Visual Basic Editor with Alt+F11. You can also use the Developer tab’s Visual Basic command; if Developer is hidden, enable it through File > Options > Customize Ribbon. In the editor, choose Insert > Module, paste the procedure, return to Excel, and run it from the Macros dialog or the editor.
On Mac, Excel also includes VBA, though the available keyboard shortcut can depend on the keyboard and function-key settings; Option+F11 or Fn+Option+F11 may open the Visual Basic Editor. The Developer tab’s Visual Basic command is another route when that tab is enabled. Excel for the web can display a workbook with VBA, but it cannot create, edit, or run VBA macros; open the file in the desktop Excel application to use this code.
For a macro that formats only a selected range, replace the fixed range reference with Selection, but first ensure the selection contains only the intended cells. A fixed worksheet and range is usually safer for a repeatable process because it does not depend on which cells happen to be selected when the macro runs.
Frequently Asked Questions
What is the VBA code for dd/mm/yyyy?
Use Range("A1").NumberFormat = "dd/mm/yyyy" for one cell, or replace A1 with the required range. The cells must contain date values for the date format to display as expected.
Does dd/mm/yyyy change the actual date?
No. It changes the display format, not the underlying date value. A genuine Excel date remains available for arithmetic, sorting, and other date operations.
How do I force the slash to display?
Use escaped slash characters in the number format, such as dd\/mm\/yyyy, when you need literal slash marks rather than the regional date separator. Check the appearance on the computers where the workbook will be used.
Can VBA convert text dates to real dates?
It can, but the conversion must use the text’s known order. A string like 04/05/2025 is ambiguous without knowing whether it means 4 May or April 5, so do not convert it by guessing from the display format.
Will the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; the applied cell formatting can still be visible in the web version.
Can I use this format for dates in a whole column?
Yes. Set the column’s NumberFormat property, such as ws.Columns("B").NumberFormat = "dd/mm/yyyy". This also formats blank cells and future entries in that column.
Related
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Close a Workbook with VBA: Safe Methods and ExamplesTo close a workbook with VBA, call the workbook’s Close method and state whether to save changes: for…
- VBA Macro Error Handling: Patterns, Examples, and FixesVBA macro error handling lets a procedure respond to runtime problems instead of stopping without explanation. Use On…
- VBA Error Handler: How On Error Works and When to Use ItIn VBA, an error handler is code that responds when a statement fails while a procedure is running.…
- Excel VBA AutoFilter Example: Code and TipsTo filter a worksheet with VBA, use the AutoFilter method on a range that includes its header row.…
- Excel VBA: Copy and Paste Values Across a WorksheetTo replace formulas with their current results across a worksheet in Excel for Microsoft 365 on Windows, run…
- VBA Range Set: How to Set and Use Excel Cell RangesIn Excel VBA, use the Set keyword to assign a cell or range reference to an object variable:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.