Excel VBA Date Number Format: How to Set It

In Excel VBA, format a cell or range as a date by setting its NumberFormat property, for example: Range("B2:B100").NumberFormat = "dd-mmm-yyyy". This changes how the cell’s numeric value is displayed; it does not convert text into a real Excel date or change the underlying date value.

What Excel means by a date number format

Excel stores real dates as serial numbers, with times represented as fractions of a day. A date number format tells Excel how to display that stored value. For instance, the same date can appear as 3/8/2025, 08-Mar-2025, or 2025-03-08, depending on the format. Changing the display format normally leaves the date’s value intact, so formulas that compare or calculate with the date continue to use the same number.

This distinction matters when a cell looks like a date but does not behave like one. A value stored as text will not become a usable date just because you apply a date format. Likewise, formatting a number such as 45000 as a date displays the date corresponding to that serial in the workbook’s date system; it does not establish that the number was intended to represent a date.

Most workbooks use Excel’s 1900 date system, though a workbook can use the 1904 system. The same serial can therefore correspond to different calendar dates across workbooks using different systems. If dates are transferred between workbooks, particularly through code that copies raw numeric values, check that both workbooks use compatible date systems.

Use NumberFormat in VBA

Range.NumberFormat is usually the right property for a macro that should apply a consistent format regardless of the computer’s regional display conventions. The format string uses Excel’s custom number-format syntax, not a VBA date format or a date written as text. For example, yyyy-mm-dd sets a year-month-day display.

This example assumes the worksheet is named Data, row 1 contains a heading, and B2:B100 contains actual Excel date values. It formats those cells as day, abbreviated month, and four-digit year:

Option Explicit

Sub FormatDateColumn()
    Dim ws As Worksheet

    ' Select the worksheet and format the date values.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("B2:B100").NumberFormat = "dd-mmm-yyyy"
End Sub

Using a worksheet variable and a fully qualified range helps avoid a common macro error: applying a format to the active sheet when the intended sheet is not active. Change the sheet name and address to match the workbook. Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z.

If the range is already selected, Selection.NumberFormat can work, but it depends on the user’s current selection and is therefore less predictable in reusable code. For a one-cell format, use a specific reference such as ws.Range("B2").NumberFormat = "mmm d, yyyy".

Choose a format code that fits the data

Use a format that makes the date unambiguous for the people reading the workbook. A numeric format such as 03/08/2025 can mean March 8 or August 3 in different regional conventions. An ISO-style display, yyyy-mm-dd, avoids that ambiguity in many business and data-transfer contexts. A format such as d mmm yyyy is also easy to read: 8 Mar 2025.

Common Excel date and time format codes include:

m/d/yyyy displays a short month/day/year date, such as 3/8/2025.
dd-mmm-yyyy displays 08-Mar-2025.
yyyy-mm-dd displays 2025-03-08.
mmmm d, yyyy displays March 8, 2025.
h:mm AM/PM displays a time such as 2:30 PM.
yyyy-mm-dd h:mm displays both date and time, such as 2025-03-08 14:30.

In Excel format codes, m can mean month or minute depending on its position. Near hour or second codes it represents minutes, as in h:mm; in a date pattern it represents the month. Use mm for a two-digit month or minute when the context makes the meaning clear. The format code mmm gives an abbreviated month name, while mmmm gives the full month name.

Formatting can also hide information. If a cell contains a date and time but the format displays only the date, the time remains in the value. Calculations and sorting may still reflect the time component. If the time should be removed from the data, that is a separate value-changing operation; changing the number format only changes what is shown.

Format a date while assigning it

A macro can set a genuine date value and its display format separately. This is useful when creating a date from known year, month, and day components. DateSerial avoids interpreting an ambiguous date string according to the computer’s regional settings.

This example assumes the worksheet named Data exists and B2 is the cell that should receive the date. It writes March 8, 2025, as a date value and displays it as 08-Mar-2025:

Option Explicit

Sub WriteAndFormatDate()
    Dim ws As Worksheet

    ' Save a copy of the workbook before running this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("B2").Value = DateSerial(2025, 3, 8)
    ws.Range("B2").NumberFormat = "dd-mmm-yyyy"
End Sub

DateSerial(year, month, day) constructs a date from numeric components. By contrast, assigning a string such as "03/08/2025" can be interpreted differently depending on regional date conventions. For imported text dates, first determine the source’s date order and convert the text deliberately; applying NumberFormat alone cannot perform that conversion.

Excel’s Value and Value2 properties are also worth distinguishing. Value can return a VBA Date subtype when a cell is date-formatted. Value2 returns the underlying value without using the Currency and Date subtypes, so a date commonly comes through as a number. Neither property is a number format: use NumberFormat to control the cell’s display.

NumberFormat versus NumberFormatLocal

NumberFormat is generally preferable when code should use a stable format code across regional settings. NumberFormatLocal uses the number-format code in the language and regional conventions of the current Excel installation. That can be useful when a macro is specifically intended to work with a user’s localized format strings, but it can make code less portable if the workbook moves between computers.

For example, format tokens and separators can be represented differently in localized Excel installations. If a macro expects the same display pattern on different machines, use NumberFormat and an unambiguous pattern such as yyyy-mm-dd. If you use NumberFormatLocal, verify the resulting display in the target language and regional settings rather than assuming the format string will be identical everywhere.

Neither property changes a text string into a date, and neither should be confused with the VBA functions used to create or interpret date values. Keep the tasks separate: convert or assign a valid date value, then choose how Excel should show it.

Apply the format without writing a macro

For a one-time change in Excel for Microsoft 365 on Windows, select the cells and open Format Cells with Ctrl+1. Choose the Number tab, then Date for a built-in date format or Custom to enter a code such as yyyy-mm-dd. You can also use the number-format dropdown on the Home tab for common choices. A custom format is useful when the built-in list does not show the exact pattern required.

On Excel for Mac, Command+1 opens Format Cells; the Windows shortcut is Ctrl+1. The ribbon and dialog details can vary slightly by platform and release, but the cell-formatting concept is the same. Excel for the web can display and apply many cell formats, but it cannot run or edit VBA macros. Use the desktop Excel app to work with VBA code.

Use a manual format when a small, stable range only needs a one-off change. Use VBA when the operation must be repeated, applied to changing ranges, or included as part of a larger workflow. A macro is not automatically better: for a single adjustment, the Format Cells dialog is often simpler and less risky.

Common problems and how to diagnose them

The cell still shows the original text. The content may be text rather than an Excel date number. Confirm the value is a real date and convert it using the correct source date order before setting the display format.
The date appears as a large number. The cell may have General or a numeric format. Apply a date format. The number may be an Excel date serial, but verify that interpretation before relying on it.
The displayed day and month seem reversed. A pattern such as m/d/yyyy is ambiguous to readers in different regions. Choose a clearer pattern such as dd-mmm-yyyy or yyyy-mm-dd.
The macro formats the wrong sheet. An unqualified range may refer to the active sheet. Refer to the intended worksheet explicitly, as in ws.Range("B2:B100").
The date looks right, but sorting is wrong. Text dates can sort alphabetically rather than chronologically. Convert them to true date values before sorting or calculating.
The displayed date changes after copying between workbooks. Check whether the workbooks use different 1900 and 1904 date systems, especially if code transfers raw numbers rather than copying cells with their values and formats.

For a quick diagnosis, select a problem cell and inspect its formula bar and number format. If changing the format to General reveals a number, the cell likely contains a numeric value. If it continues to show date-like characters as text, conversion is needed. This is a clue rather than proof of the intended meaning: a numeric value could be an ID, quantity, or other number, so check the source data before treating it as a date.

Frequently Asked Questions

What is the VBA code to format a cell as a date?
Use a range’s NumberFormat property, for example Range("A1").NumberFormat = "dd-mmm-yyyy". In reusable code, qualify the range with its worksheet so the format is applied to the intended cell.

How do I format a whole column as a date in VBA?
Set the property on the desired range, for example ws.Range("B:B").NumberFormat = "yyyy-mm-dd". If the first row is a heading, you may prefer a range starting at row 2 so the heading is not included. Formatting a full column does not turn text values into real dates.

Does NumberFormat change the actual date?
No. It changes how a numeric value is displayed. The underlying value remains the same unless your code separately assigns or transforms that value.

Why does VBA format a date but leave the cell unchanged?
The cell may contain text, the code may be targeting another worksheet, or the range reference may not match the cell you are checking. Confirm the target range and whether the value is a real Excel date before changing the format.

Should I use NumberFormat or NumberFormatLocal?
Use NumberFormat for a more portable, consistent format code in most macros. Choose NumberFormatLocal only when the local Excel language and regional format conventions are specifically required.

Practical rule of thumb

First make sure the cell contains a valid Excel date value; then apply the display pattern with NumberFormat. Prefer a pattern that communicates the date order clearly, qualify worksheet references in macros, and use DateSerial when constructing dates from numeric components. That separation between the stored value and its display is the key to getting reliable date formatting in VBA.

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.