How to change date formats in Excel

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.

Selected cells in Excel.

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

Click the down arrow next to the text box.

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.

Click More Number Formats.

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.

The type and options of the local date format.

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.

Selected cells in Excel.

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.

Click Custom.

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

The code of the date format.

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:

Custom formatted dates.

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.

Similar Posts

Leave a Reply

Your email address will not be published.