To format a date and time in an Excel cell with VBA, set the cell or range’s NumberFormat property, for example: Range("A2:A20").NumberFormat = "yyyy-mm-dd hh:mm". This changes how valid date-time values appear, not the underlying values; use Excel’s desktop app to run VBA, because Excel for the web cannot run macros.
How Excel stores dates and times
Excel stores dates as serial numbers and times as fractions of a day. A cell containing both is one numeric value: the whole-number portion represents the date, and the fractional portion represents the time. For instance, a value can represent a particular date at 6:30 AM even though the cell displays only the date. Applying a format that includes hours and minutes reveals the time portion if it is present.
This distinction matters because formatting is not the same as changing or creating a date-time value. Setting NumberFormat cannot turn arbitrary text into a genuine Excel date. It also does not add a time that was never stored. If a cell holds a date with no time component, a format that includes hours will normally show midnight.
For ordinary display changes, a custom number format is usually the right tool: the cell remains numeric, so Excel can still sort it chronologically, calculate elapsed time, and use it in date calculations. If you instead convert the value to text, those operations may behave differently.
Set NumberFormat in a VBA macro
Use the NumberFormat property on a cell or range. The format string uses Excel’s custom number-format syntax. This example formats the used dates in column A on a worksheet named Data, starting below a header in row 1:
Sub FormatDateTimeColumn()
Dim ws As Worksheet
Dim lastRow As Long
' Work with the specified sheet in this workbook.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Do nothing if there are no data rows below the header.
If lastRow < 2 Then Exit Sub
' Display an ISO-style date and 24-hour time.
ws.Range("A2:A" & lastRow).NumberFormat = "yyyy-mm-dd hh:mm"
End Sub
Change the sheet name, column, and starting row to match the workbook. If the data includes blanks between entries, finding the last used row with End(xlUp) still finds the last nonblank cell in column A, but it does not identify the last row based on other columns. Choose the column that reliably contains a value for every record, or set the range explicitly when its size is known.
Before running any macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even if a macro only changes formatting. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Option+F11, sometimes with Fn depending on keyboard settings, or find the editor under Tools > Macro. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro.
Choose a date-and-time format
The characters in the format string control what people see. Common choices include:
yyyy-mm-dd hh:mmdisplays a four-digit year, two-digit month and day, and 24-hour hours and minutes, such as 2026-11-03 14:05.m/d/yyyy h:mm AM/PMdisplays a compact date and a 12-hour clock, such as 11/3/2026 2:05 PM.ddd, mmm d, yyyy h:mm AM/PMincludes weekday and abbreviated month names, such as Tue, Nov 3, 2026 2:05 PM.yyyy-mm-dd hh:mm:ssincludes seconds. Add seconds when they matter to the data; otherwise they can make a table harder to scan.yyyy-mm-dd "at" hh:mminserts the literal word “at” between the date and time.
To apply one of these formats to a known range, use a statement such as Range("B2:B100").NumberFormat = "m/d/yyyy h:mm AM/PM". In a macro, qualify Range with a worksheet when there is any chance another sheet is active, for example ws.Range("B2:B100"). Unqualified references can affect the active worksheet rather than the one intended.
Understand the month-and-minute distinction
A frequent source of confusion is the letter m. In Excel number formats, it can mean month or minute depending on context. In a date such as yyyy-mm-dd, it means month. In a time such as hh:mm, it means minute because it follows the hour component. A format like mm/dd/yyyy hh:mm therefore uses mm for month in the date and minutes in the time.
Seconds use s or ss. If the displayed result looks as though minutes have become months, check the format string’s surrounding characters and ensure the time section is written in a recognizable order, such as hh:mm:ss. Do not replace every m with another code: the same format can need m for the date and for minutes in the time section.
NumberFormat versus VBA Format
Range.NumberFormat controls how a numeric cell appears while keeping the value numeric. VBA’s Format or Format$ function instead returns a formatted string. Use the property when the goal is to display dates and times in worksheet cells while preserving their calculation behavior. Use the function when you need text for a message, filename, or other string output.
For example, this VBA statement formats the current date and time as text; it does not set a cell’s number format:
Sub MakeDateTimeText()
Dim displayText As String
' Format$ returns a string rather than changing a worksheet cell.
displayText = Format$(Now, "yyyy-mm-dd hh:mm")
MsgBox displayText
End Sub
When formatting with a cell property, use Excel’s format codes. When creating text with VBA’s formatting function, consult VBA’s date-format rules; some codes and their interpretation can differ between the two contexts. A string created by Format$ will not automatically remain a date serial for later calculations.
Check the cell value and diagnose common problems
If changing NumberFormat has no visible effect, first check whether the cell contains a real Excel date-time number or text that merely looks like one. A text value will not respond to numeric date formatting in the expected way. Imported data may contain text dates, spaces, or formats that Excel does not recognize for the current regional settings. Convert or parse such values separately before applying a display format; do not assume that formatting alone fixes the source data.
- The time is missing: Check that the stored value includes a time fraction. If it does not, the time portion may display as 00:00 or 12:00 AM, depending on the format.
- The time shows as a decimal: Apply a date-time number format to the numeric value. A fractional number by itself is Excel’s representation of a time of day.
- The cell shows hashes: The column may be too narrow for the displayed value. Widen the column or choose a shorter format; hashes do not necessarily mean the date value is invalid.
- The date appears shifted: Check the source value and the workbook’s date system or import process. A number format changes appearance, not time zones, and Excel dates do not carry a time-zone identity.
- The macro formats the wrong cells: Qualify the range with the intended worksheet and workbook rather than relying on whichever sheet happens to be active.
You can verify whether a cell is numeric by selecting it and checking its value in the formula bar, or by using a temporary VBA check such as IsDate for values that should represent dates. That check can help identify likely date values, but it does not guarantee that every text format will be interpreted as intended. Review imported data and test representative rows before applying a conversion to a whole column.
Run and test the macro safely
In Windows Excel, press Alt+F11 to open the Visual Basic Editor, insert a standard module with Insert > Module, and paste the procedure. Return to Excel, then run it from the Macro dialog (Alt+F8) or from the editor. On Mac, use the Visual Basic Editor and macro tools available in the desktop app; keyboard shortcuts can depend on the keyboard’s function-key settings. The Excel for the web app can display a workbook with macros but cannot run VBA.
Test on a small, representative range before applying a format to a large dataset. Check a row with a time near midnight, a row with seconds if those are relevant, and a blank or unusual row. Confirm both the displayed result and that the cell still behaves as a number, for example by changing its format or using it in a calculation. This catches cases where the incoming values are text rather than serial date-times.
For a one-time change without a macro, select the cells and open Format Cells with Ctrl+1 on Windows or Command+1 on Mac. Choose the Number tab, select Custom, and enter an Excel format such as yyyy-mm-dd hh:mm. A macro is useful when the same range needs consistent formatting repeatedly, but it is not required for a simple manual adjustment.
Frequently Asked Questions
How do I show seconds in an Excel VBA date format?
Set the range’s NumberFormat to a pattern such as yyyy-mm-dd hh:mm:ss. This displays seconds already present in the underlying date-time value; it does not create more precise data.
How do I display AM or PM with VBA?
Use a format such as m/d/yyyy h:mm AM/PM in the range’s NumberFormat property. Excel will display a 12-hour time with the appropriate AM or PM label.
Does NumberFormat change the actual date or time?
No. It changes the display format while leaving a valid numeric date-time value intact. To change the stored value, use a separate value assignment or date-time calculation.
Why does VBA formatting not work on my date text?
Because text is not necessarily an Excel date serial. Convert the source text to a genuine date-time value using an approach appropriate to its exact format and regional conventions, then apply NumberFormat.
Can I use this VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app to run VBA; you can also apply a number format manually in the web app where the formatting controls are available.
Related
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
- Excel Table Data Filtering: How to Narrow Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Toggle a Logitech Macro in G HUBTo toggle a Logitech macro on and off, open Logitech G HUB, select your supported device, create or…
- Excel VBA Error Message Box: Show and Handle ErrorsAn Excel VBA error message box is usually created with the MsgBox function; use On Error GoTo to…
- Excel VBA Object Required Error: Causes and FixesIn Excel VBA, “Object required” is run-time error 424: VBA reached a statement that needs an object, such…
- Excel Macro Will Not Run? Fix the Common CausesIf an Excel macro will not run, first check that the workbook is saved as a macro-enabled file,…
- Excel VBA: Hide a Column RangeTo hide a range of columns with Excel VBA, set its Hidden property to True: use Columns("B:D").Hidden =…
- Excel VBA Date Variable: Types, Examples, and TipsIn Excel VBA, declare a date variable with Dim myDate As Date. A VBA Date variable can hold…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.