Excel VBA Format Cell as Date: 4 Ways

VBA & macros · Updated November 22, 2026

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 NumberFormat but 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. Use NumberFormat with 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 Date1904 property 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.