How to change date formats in Excel

Excel uses the same date format as the computer system settings. You may want to change the date format, however, to make it more accessible for users in other regions or to make your data more compact.

Change the date format

To get started, open the Excel spreadsheet that contains the dates you want to reformat. Select the cells that contain each date by clicking and dragging the mouse over them.

Once selected, click the down arrow to the right of the text box in the Number group on the Home tab.

A drop-down menu will appear. There are two date format options to choose from in this menu: Short date (1/17/2021) and Long date (Sunday January 17th 2021). You can select one of these or, if it is not the format you are looking for, click on “Other number formats” at the bottom of the menu. This opens the Format Cells window. You can also press Ctrl + 1 (Command + 1 on Mac) to open this window.

You will now be in the Date category of the Format Cells window. In the Type group, select the format you want to use. If you’d prefer to choose a date format based on how a particular language and region formats dates, you can select an option from the Locale drop-down menu.

Click “OK” at the bottom of the window when you have selected the format you want to use. The dates in the previously selected cells will change to the new format.

Create and use your own custom date format

There are many different formats to choose from, but you can also create your own custom date format if you like. To do this, select the cells that contain the dates you want to format by clicking and dragging the cursor over them.

Then, press Ctrl + 1 (Command + 1 on Mac) to open the Format Cells window. You will automatically be in the Date category. Click “Custom” at the bottom of the category list.

Next, you will notice a code in the Type text box.

You can modify this code to create your own custom format. Each letter or letters in the code represents a type of view. Here’s what each letter or letters mean:

Code	View as:
m	Months: 1-12
mm	Months: 01-12
mmm	Months: January-December
Mmmm	Months: January-December
mmmmm	Months: the first letter of the month
D.	Days: 1-31
dd	Days: 01-31
ddd	Days: Sun-Sat
dddd	Days: Sunday-Saturday
yy	Years: 00-99
yyyy	Years: 1900-9999

So, should you go inside m/d/yyin the Type box and then click “OK”, the dates in the selected cells would be formatted as:

Play around with the different combinations of codes to find the perfect format that works for your spreadsheet.

When you change the date format, consider who will be looking at the spreadsheet. If your spreadsheet contains a large amount of data and you want to examine the data over a period of time, you can sort the data by date, regardless of the format used.