To format a cell as a date in Excel VBA, set the cell’s NumberFormat property to a date format string, for example Range("A1").NumberFormat = "mm/dd/yyyy". This changes only the display, not the underlying value, and works for any cell or range.
Why NumberFormat Matters
Excel stores dates as serial numbers—integers counting days from January 1, 1900 (or 1904 on Mac). For instance, 45292 represents December 31, 2023. The NumberFormat property tells Excel how to display that serial number. Without a date format, the cell shows a number like 45292, which confuses anyone reading the sheet. Setting the format is essential for readability, but it does not convert text to a date; that requires a separate step.
Setting a Date Format with VBA
The simplest way is to assign a format string directly. Here is a basic example:
Sub FormatDate()
' Assumes cell A1 contains a real date value
Range("A1").NumberFormat = "mm/dd/yyyy"
End Sub
You can apply it to a range at once:
Range("A2:A100").NumberFormat = "dd-mmm-yy"
Common format codes include:
"mm/dd/yyyy"→ 12/31/2023"dd-mmm-yyyy"→ 31-Dec-2023"yyyy-mm-dd"→ 2023-12-31 (ISO style)"mmm d, yyyy"→ Dec 31, 2023
Remember that the format string uses m for month and d for day; mm forces two digits. For 12-hour time, use h:mm AM/PM.
Using the NumberFormatLocal Property
NumberFormat uses English (US) format codes regardless of the user’s locale. If you want the format to match the regional settings of the computer (e.g., dd/mm/yyyy in the UK), use NumberFormatLocal. For example:
Range("B1").NumberFormatLocal = "dd/mm/yyyy"
On a system with UK regional settings, this displays 31/12/2023. On a US system, it might display 12/31/2023 because the format is interpreted according to the locale. Use NumberFormat for consistency across machines, or NumberFormatLocal when you want to respect local conventions.
Formatting a Range with a Loop vs. One Line
New VBA users often loop through cells to format each one:
For Each cell In Range("A1:A10")
cell.NumberFormat = "mm/dd/yyyy"
Next cell
This works but is unnecessary. Applying the format to the entire range at once is faster and cleaner:
Range("A1:A10").NumberFormat = "mm/dd/yyyy"
Loops are only needed if you want different formats for different cells based on conditions, but even then you can use conditional formatting or a single format with placeholders.
Converting Text to a Real Date Value
Formatting does not change the underlying value. If your cell contains text like “12/31/2023” as a string, setting NumberFormat will not make it a date. You must convert it first. Use CDate or DateValue:
Range("A1").Value = CDate(Range("A1").Value)
Range("A1").NumberFormat = "mm/dd/yyyy"
Or for a range, loop:
For Each cell In Range("A1:A10")
If IsDate(cell.Value) Then
cell.Value = CDate(cell.Value)
cell.NumberFormat = "mm/dd/yyyy"
End If
Next cell
The IsDate check prevents errors on non-date text. After conversion, the cell contains a numeric date serial, and the format displays it correctly.
Common Problems and Troubleshooting
Several issues arise when formatting dates in VBA:
- Cell shows #####: The column is too narrow. Widen it or use a shorter format.
- Format doesn’t stick: If you set
NumberFormatbut the cell still shows a number, the cell may contain text. Convert it as shown above. - Date changes after re-opening: If you use a format like
"m/d/yyyy", Excel might interpret it differently on another system. UseNumberFormatwith a specific locale-independent code like"yyyy-mm-dd"to avoid ambiguity. - Mac vs. Windows differences: The 1904 date system on Mac can shift dates by 4 years. If you share files, set the workbook’s
Date1904property consistently or use ISO formats.
Alternatives: Using the Format Cells Dialog
If you don’t need VBA, you can format cells manually: select the cells, press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells, go to the Number tab, choose Date, and pick a format. To replicate this in VBA, you can use the NumberFormat property as described. For a one-time action, the manual method is simpler; VBA is for automation or dynamic formatting.
Frequently Asked Questions
How do I format a cell as a date in VBA without changing the value?
Use Range("A1").NumberFormat = "mm/dd/yyyy". This only changes display; the underlying serial number remains the same.
Why does my VBA date format show as text?
If the cell contains text, setting the format won’t convert it. First use CDate or DateValue to turn the text into a real date value, then apply the format.
Can I format a date with time using VBA?
Yes. Use a format like "mm/dd/yyyy hh:mm" or "dd-mmm-yy h:mm AM/PM". The underlying value still includes the time fraction.
How do I format an entire column as date in VBA?
Use Columns("A").NumberFormat = "mm/dd/yyyy" or Range("A:A").NumberFormat = "mm/dd/yyyy".
Is there a way to use the system’s short date format in VBA?
You can read the system’s format using Application.International(xlShortDate) and assign it: Range("A1").NumberFormat = Application.International(xlShortDate). This respects the user’s locale.
Does formatting a date affect calculations?
No, calculations use the underlying serial number. Formatting only affects how the value is displayed, so you can still subtract dates or use them in formulas.
Related
- Excel Spreadsheet Checklist Template: How to Build One
- Excel Strikethrough: How to Add It & Shortcuts
- Excel Strikethrough: How to Apply & Remove It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Values: How to Change an Item SafelyYes. In VBA, change a Dictionary value by assigning a new value to its key, for example dict("North")…
- VBA Find a Row With a Specific Value in an Excel SheetTo find a row with a specific value in Excel VBA, use the Find method on the relevant…
- Excel VBA Error Handling: On Error, Examples, and Best PracticesExcel VBA error handling lets a macro respond to runtime problems instead of stopping unexpectedly. Use On Error…
- Excel Macro Step Into: How to Debug VBA Line by LineIn Excel VBA, Step Into runs a macro one statement at a time and enters any procedure or…
- Excel VBA: How to Edit a Worksheet with VBA MacrosTo edit a worksheet with Excel VBA, write a macro that identifies the worksheet and changes its cells,…
- VBA Turn Off AutoFilter in Excel Without Losing DataTo turn off a worksheet AutoFilter in VBA, first show all rows if a filter is active, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.