How to Change the Date Display Format in Excel: From 2026/1/1 to January 1 and More

You have a date in your Excel sheet that looks like 2026/1/1, but you need it to display as January 1. Excel stores all dates as serial numbers, which allows for calculations. The way a date appears is controlled by its display format. This article explains how to change that format to any style you need.

Understanding Excel Date Formats

Excel does not store dates as text like “January 1.” Instead, it stores them as sequential serial numbers where January 1, 1900, is number 1. The number 50000 represents June 6, 2036. This system enables date arithmetic, such as calculating the number of days between two dates. The format you apply is a visual layer that tells Excel how to present that underlying number as a recognizable date. Changing the format does not alter the stored value, only its display.

Built-in formats cover most common styles, such as “1/1/2026,” “01-Jan-2026,” or “January 1, 2026.” When these are insufficient, you can design a custom format using specific codes. For example, “mmmm d” displays as “January 1,” and “dddd, mmmm d, yyyy” displays as “Thursday, January 1, 2026.” It is essential to ensure your cell data is a true date serial number, not text that looks like a date, for formatting to work correctly.

Steps to Apply a New Date Format

You can change a date’s appearance using the ribbon, a keyboard shortcut, or a formula. The method you choose depends on whether you need a permanent cell format or a text result for another purpose.

Method 1: Using the Format Cells Dialog

This method permanently changes the display format of the cells you select.

1. Select your date cells
   Click on the cell or drag to select a range of cells containing the dates you want to format.
2. Open the Format Cells dialog
   Press Ctrl+1 on your keyboard. You can also right-click the selected cells and choose Format Cells from the menu.
3. Choose the Date category
   In the dialog box, click the Number tab. Select Date from the Category list on the left.
4. Select your preferred format
   Scroll through the Type list on the right to see built-in options like *March 14, 2012 or 14-Mar-12. The Locale dropdown can show region-specific formats. Click OK to apply.

Method 2: Using the Home Tab Ribbon

This provides faster access to the most common date formats.

1. Select your date cells
   Highlight the cells containing the dates you wish to change.
2. Open the Number Format dropdown
   On the Home tab, find the Number group. Click the dropdown menu that likely shows “General” or “Short Date.”
3. Choose a format
   Select either Short Date or Long Date from the list. For more options, click More Number Formats at the bottom to open the Format Cells dialog.

Method 3: Creating a Custom Date Format

Use this when no built-in format matches your needs, such as displaying only the month and day.

1. Open the Format Cells dialog
   Select your cells and press Ctrl+1.
2. Select the Custom category
   In the Format Cells dialog, click Custom in the Category list on the left.
3. Enter your format code
   In the Type box, you will see the current format code. Replace it with your custom code. For “January 1,” type mmmm d. For “01-Jan,” type dd-mmm. The preview above the Type box shows the result.
4. Apply the format
   Click OK. Your selected cells will now display dates using your custom pattern.

Method 4: Using the TEXT Function

This function converts a date into formatted text, useful in formulas or when you need a text result.

1. Start the formula
   In a new cell, type an equals sign followed by TEXT, like this: =TEXT(
2. Reference the date cell and add the format
   Click on the cell with your original date, then type a comma. Inside quotation marks, enter your format code. A complete formula looks like this: =TEXT(A1, "mmmm d")
3. Complete the formula
   Press Enter. The cell will show the date from cell A1 formatted as “January 1.” The result is text, not a date you can calculate with.

Common Mistakes and Formatting Limits

Cell Shows ##### After Formatting

This means the column is too narrow to display the formatted date. The underlying date value is fine. Simply double-click the right border of the column header to auto-fit the width, or manually drag the border to make the column wider.

Date Does Not Change Format

If applying a new format has no effect, the cell likely contains text, not a true Excel date. Text entries are left-aligned by default, while dates are right-aligned. To fix this, use the DATEVALUE function to convert the text to a date serial number, or use Text to Columns (Data > Data Tools > Text to Columns) and finish the wizard without changing any settings.

Custom Format Shows Month as a Number

In custom format codes, “m” alone represents the minute in time contexts. To display the month as a number without leading zeros, use “m”. To display it with leading zeros, use “mm”. To display the full month name, you must use “mmm” for the abbreviated name or “mmmm” for the full name. Using the wrong code will give unexpected results.

Built-in Formats vs. Custom Formats: Key Differences

Item	Built-in Date Formats	Custom Date Formats
Access Method	Home tab dropdown or Date category in Format Cells	Custom category in Format Cells dialog
Flexibility	Fixed list of common regional styles	Unlimited combinations using format codes like d, mmm, yyyy
Locale Support	Formats change automatically based on Windows region settings	Codes are universal; display is consistent across systems
Best For	Standard business reports, invoices, and regional compliance	Specific branding needs, condensed reports, or non-standard layouts
Example Output	1/1/2026, 01-Jan-26, Thursday, January 01, 2026	Jan-1, Q1-26, 2026-W01, 01 January

You can now change any date in your worksheet from a basic number to a clearly formatted label. Use the Format Cells dialog for permanent changes or the TEXT function for dynamic text results. For advanced control, experiment with custom format codes like “yyyy-mm-dd” for ISO standards. Remember that pressing Ctrl+Shift+# applies the default short date format instantly.