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 the date as it is very important to analyze the 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 click on the “Number” group.
- After that, click on the Custom drop-down box from the number group, and appears the “Long Date” and “Short Date” options.
- The Long Date format is displayed as (02 January 2000) and the Short Date looks like (02-01-22). You can either choose one of these.
There are “More Number Formats” option which is at the bottom of it. Click here to get more date formats.
Once you click, the format cell window opens, and there you need to choose the Date category.
Here are the date format types, locations, and language shown along with its sample. You can select the particular format and it shows the sample that gives you a hint of how it looks on the worksheet.
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 the format as per your need then 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 cell 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 into 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 |
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.