30 Advanced Excel Tips for 2026

Excel Champs / Excel Basics / How to Change Date Format in Excel
By Puneet Gogia (Microsoft MVP)

How to Change Date Format in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Select the dates, press Ctrl + 1, choose Custom and type a code in the Type box, such as dd-mmm-yyyy for 05-Jan-2026 or mm/dd/yyyy for 01/05/2026. For a quick change, pick Short Date or Long Date from the Home → Number Format drop-down.

The codes to combine: d or dd for the day, ddd or dddd for Mon or Monday, m or mm for the month number, mmm or mmmm for Jan or January, and yy or yyyy for the year. dddd, d mmmm yyyy shows Monday, 5 January 2026.

Short Date and Long Date follow your Windows regional settings, so the same file can show 05/01/2026 on one PC and 1/5/2026 on another. Use a custom code when the layout must stay fixed.

If the format doesn’t change, the dates are stored as text, and they usually sit left-aligned. Select the column, go to Data → Text to Columns, click Next twice, choose Date with the order your data uses (DMY or MDY) and click Finish. A cell showing #### is a real date in a column that’s too narrow.

To get a formatted date as text for labels or for joining with other text, use =TEXT(A2,"dd-mmm-yyyy"). The result is text, so it no longer sorts or calculates as a date.

One of the best things about Excel is that you can change date formats and apply a format according to your need or the way you want to show it to the user.

In this tutorial, we’ll learn how to change the format of a date, as dates are very important when you analyze sales, profits, and events.

Steps to Change Date Format in Excel

Below are the steps to quickly change the format of the date in Excel.

  1. First, select the cell or range of cells where you would like to change the date format.
  2. Next, go to the “Home” tab and then to the “Number” group.
  3. After that, click on the Number Format drop-down box in the Number group (it shows the current format, like General or Custom), and you’ll see the “Long Date” and “Short Date” options.
    select-short-long-date-date-format
  4. The Long Date format shows the date in full (like 02 January 2022), and the Short Date shows it in numbers (like 02-01-22). The exact look of both depends on your system’s regional settings. You can choose either of these.

There’s a “More Number Formats” option at the bottom of it. Click it to get more date formats.

click-on-more-number-formats

Once you click, the Format Cells dialog box opens, and there you need to choose the Date category.

dialog-box-with-date-formats

Here you have the date format types and the Locale (location) list, along with a sample. When you select a format, the sample gives you a hint of how it will look on the worksheet. The formats that start with an asterisk (*) change with your system’s regional settings; the others stay the same on every computer.

In the end, click the “OK” button after finalizing the date format you want to use.

select-final-format-date-format

Create Your own Custom Date Format

No doubt, there are several formats available by default. But in case you want to make a format as per your need, you can make it by using some specific codes.

    1. To do this, you have to first select the range of cells that contain the dates.
    2. Then, press (Ctrl+1) to open the Format Cells dialog box and click on the “Custom” option which is at the bottom of the category list.
      use-custom-option-date-format
    3. Now, you’ll notice there is a specific code in the Type text box, and you can re-create or edit this code to make your own format.
    4. Once you’ve done this, click “OK”, and the selected cells will change to the new format.

check-sample-date-format

The moment you click OK, it changes the date format to the custom format that I have specified.

final-screen-with-date-format

You can use the following table to create your custom date.

Code
Describes:
Example
dd
Day number
01
mm
Month number
02
yyyy
Full-year
2022
mmm
Month name(short)
Jan
yy
Year last two digits
22

And here are a few more codes that you can combine with the ones in the table:

  • d: day number without a leading zero (2).
  • ddd: short day name (Sun).
  • dddd: full day name (Sunday).
  • m: month number without a leading zero (1).
  • mmmm: full month name (January).
  • mmmmm: first letter of the month (J).

For example, the custom format dddd, mmmm d, yyyy shows 02-Jan-2022 as Sunday, January 2, 2022.

Keyboard Shortcut to Change the Date Format

If you just want a quick date format, select the cells and press Ctrl + Shift + # (Ctrl + Shift + 3). It applies the date format with the day, month and year, like 2-Jan-22.

Change the Date Format with the TEXT Function

When you need the date in a specific format inside a formula, like when you join it with text, use the TEXT function with the same format codes:

=TEXT(A1,"dd-mmm-yyyy")

This returns 02-Jan-2022 for the date 2 January 2022. Keep in mind that TEXT returns text, not a date, so you can’t use the result in date calculations.

Date Format Not Changing?

If the format doesn’t change when you apply it, the most common reasons are:

  • The date is stored as text: a text value doesn’t change with a number format. You can check it with the ISTEXT function or by the left alignment. Convert it with Data ➜ Text to Columns (choose Date in step 3) or with the DATEVALUE function.
  • The column is too narrow: when you see ##### in the cell, the date doesn’t fit. Make the column wider.
  • The system date settings: the Short Date and Long Date formats come from your Windows or Mac regional settings, so change them there if you want a different default.

Frequently Asked Questions

How do I change the default date format in Excel?

Excel takes the default date format from your system. On Windows, change it in Settings ➜ Time & language ➜ Region (Regional format), and on a Mac, in System Settings ➜ General ➜ Language & Region.

How do I show the day name with the date?

Use a custom format like ddd, dd-mmm-yyyy to show Sun, 02-Jan-2022, or dddd for the full day name.

Can I change the date format for a different language?

Yes. In the Date category of the Format Cells dialog box, pick a language from the Locale (location) list, and Excel shows the month and day names in that language.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment