To keep a real Excel date/time value, set a cell’s number format with VBA, for example Range("A1").NumberFormat = "dd mm yyyy hh:mm:ss". If you need the displayed result to use spaces between every part—such as 03 11 2026 14 07 09—use VBA’s Format$ with nn for minutes and write the result as text; Excel’s custom number format can interpret mm between spaces as a month rather than minutes.
Choose between a real date and exact text
The important choice is whether the cell must remain a usable date/time value or only look like a formatted timestamp. Excel stores dates and times as numbers: the whole-number portion represents the date and the decimal portion represents the time. A number format changes how that value appears, not the underlying value. Keeping the value numeric lets you sort chronologically, calculate elapsed time, and use date functions.
There is a catch in the requested all-space pattern. In Excel number formats, m or mm means minutes when it is immediately next to an hour or seconds token in the appropriate position. With spaces separating every token, that context can be lost, so the middle mm may display the month instead. A safe numeric format uses a colon to identify the time fields: dd mm yyyy hh:mm:ss. For a literal all-space result, VBA’s date-formatting function uses nn for minutes and returns text.
Apply a custom number format with VBA
For an existing date/time value in A1, this statement keeps the value numeric and displays the day, month, year, hour, minute, and second. The date fields are separated by spaces; colons separate the time fields so Excel recognizes minutes reliably.
Range("A1").NumberFormat = "dd mm yyyy hh:mm:ss"
Use dd for a two-digit day, mm for a two-digit month in the date portion, and yyyy for a four-digit year. The time uses hh for a two-digit hour, mm for minutes, and ss for seconds. For example, a stored value corresponding to 3 November 2026 at 14:07:09 displays as 03 11 2026 14:07:09.
Format a whole range safely
To format a known range rather than one cell, assign its NumberFormat property. This example formats dates in A2:A100 without changing their values. It skips no cells deliberately: applying a number format to a blank cell is harmless, but text entries in the range will remain text and will not become real dates just because a date format was assigned.
Sub FormatDateTimeRange()
' Format date/time values in A2:A100 while keeping them numeric.
Dim targetRange As Range
Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")
targetRange.NumberFormat = "dd mm yyyy hh:mm:ss"
End Sub
Change Sheet1 and the range to match the workbook. Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is the straightforward way to recover if a macro affects the wrong cells.
Return the exact all-space pattern as text
If the required output must look exactly like 03 11 2026 14 07 09, use VBA’s Format$ function. In VBA date-format strings, nn represents minutes; mm represents a month unless it is clearly being used as minutes. This example writes the current date and time as text into A1:
Sub WriteSpacedTimestamp()
' Save a copy first; this writes text, not an Excel date serial.
Dim stamp As Date
Dim outputCell As Range
stamp = Now
Set outputCell = ThisWorkbook.Worksheets("Sheet1").Range("A1")
outputCell.Value = Format$(stamp, "dd mm yyyy hh nn ss")
End Sub
The resulting text can be copied or shown exactly as requested, but it is no longer a numeric date/time. Excel may sort text timestamps alphabetically rather than chronologically, and date arithmetic will not work as expected until the text is converted back to a date/time value. The year-first pattern is often more convenient for sorting text, but it is a different display from the requested day-first format.
Run the macro and check the result
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure by pressing F5. You can also return to Excel, press Alt+F8, select the macro, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.
On Excel for Mac, open the Visual Basic Editor from Tools > Macro > Visual Basic Editor where available, or use the Developer tab. The common editor shortcut is Option+F11; keyboard mappings can differ by Mac keyboard and system settings. Ctrl+1 on Windows opens Format Cells; on Mac, Command+1 is the usual equivalent. In the Format Cells dialog, use the Number tab and Custom category to inspect or enter a number format.
After applying the format, click the cell and inspect the formula bar. If the formula bar shows a date/time value while the cell shows a shortened or differently arranged display, the cell is likely numeric and the number format is doing its job. If the formula bar shows the full timestamp as text, it was written as text instead.
NumberFormat, NumberFormatLocal, and regional settings
NumberFormat uses Excel’s standard format-code syntax and is generally the better choice for VBA that should work across computers with different display languages. NumberFormatLocal uses the format-code conventions of the user’s Excel locale. Those conventions can affect separators and names, so local-format strings may need adjustment when a workbook moves between regional settings. Avoid assuming that a comma, slash, or month/day order will appear identically everywhere.
The format string controls the display separators you specify. It does not determine how an ambiguous typed date such as 03/11/2026 was originally interpreted. Depending on regional settings, that entry might mean March 11 or 3 November. When importing or creating dates, build a genuine date value from known year, month, and day components rather than relying on ambiguous text.
Common problems and how to fix them
- The middle field shows a month. In an all-space Excel custom format, Excel may read the middle
mmas month. Usedd mm yyyy hh:mm:ssfor a numeric date/time, orFormat$(value, "dd mm yyyy hh nn ss")when the all-space output can be text. - Seconds show as zero or do not change. The cell may contain a value without seconds, or its source formula may round or truncate the time. A display format cannot restore precision that was never stored.
- The cell shows hashes. A narrow column commonly displays hash marks when a formatted date/time does not fit. Widen the column; the hashes do not by themselves mean the value is invalid.
- The display does not change. The cell may contain text rather than an Excel date serial, or another macro may overwrite the format. Check the formula bar and confirm the macro targets the intended worksheet and range.
- The time shows 2:07 PM rather than 14:07. Add an AM/PM marker only if a 12-hour clock is intended. Without that marker,
hhdisplays the hour on a 24-hour basis in this pattern.
When to use the format in a macro
Assigning NumberFormat is useful when a report is regenerated and needs a consistent appearance each time. It is also preferable when downstream formulas, PivotTables, or chronological sorting depend on real date/time values. Apply the format to the smallest appropriate range rather than the entire worksheet, especially in a workbook with different types of data.
Use a text timestamp when an external system specifically requires fixed-width text with spaces, or when a label must be copied exactly as shown. Keep a separate numeric date/time column if the same data will also be filtered, compared, or calculated. That simple separation avoids trying to make one cell serve both as a display string and as a reliable date value.
Frequently Asked Questions
What is the VBA format code for day month year and time?
For a numeric Excel cell, use dd mm yyyy hh:mm:ss with the NumberFormat property. The colon separators make the time’s minute field unambiguous. For an exact all-space text string, use Format$(dateValue, "dd mm yyyy hh nn ss").
Can I use mm for minutes in VBA?
In Excel cell number formats, mm can mean minutes when its position clearly associates it with hours or seconds; otherwise it can mean month. In VBA’s Format$ date-format string, use nn for minutes to avoid confusion, particularly when spaces separate the fields.
Does changing NumberFormat change the date stored in the cell?
No. It changes the displayed representation, not the underlying serial value. That means a numeric cell remains available for date calculations and sorting. Writing the result of Format$ into a cell, by contrast, stores formatted text.
How do I include seconds in the displayed date and time?
Include ss in the time portion of the custom format, such as dd mm yyyy hh:mm:ss. The displayed seconds reflect the stored value; formatting cannot create seconds that the source value does not contain.
Will the macro work on Excel for Mac?
The Range.NumberFormat and VBA Format$ approaches are available in desktop Excel for Mac as well as Windows. Opening the editor and keyboard shortcuts differ, and workbook date-system settings can affect serial values, so check the target workbook when sharing files across platforms.
Related
- Excel Split Cell Value by Delimiter: Methods and Tips
- Excel Split Cells Into Multiple Columns
- Excel Split Cell Data Into Multiple Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Class Modules: A Practical How-ToTo use a class module with a VBA Dictionary, define a class to represent one record, create an…
- Excel Macro to Clear Specific Cells: Safe VBA GuideTo clear chosen cells in Excel with a macro, use VBA’s ClearContents method on a worksheet range. For…
- Excel VBA InputBox Type: Values, Examples, and UseIn Excel VBA, the Type argument belongs to Application.InputBox and controls what kind of response the dialog accepts:…
- 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 Match: How It Works and How to Use It SafelyIn Excel VBA, Match finds a value in a one-dimensional range and returns its position within that range—not…
- VBA MsgBox Default Button: Set It and Choose SafelyIn VBA, a MsgBox uses its first button as the default unless you specify otherwise. To make another…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.