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.
- First, select the cell or range of cells where you would like to change the date format.
- Next, go to the “Home” tab and then to the “Number” group.
- 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.

- 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.

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

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.

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.
- To do this, you have to first select the range of cells that contain the dates.
- 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.

- 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.
- Once you’ve done this, click “OK”, and the selected cells will change to the new format.

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

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.
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.