How to change Date format in Excel?

[vishrut-singhal]

How to change the Date Format in Excel

Microsoft Excel allows us to change the default date format and select another variety of date formats such as December 20, 2008, 20/12/2008, 12 December 2012, 24-Oct-2021, and many more.

Default Date Format in Excel

The default date format is set in the Control Panel setting, i.e. dd/mm/yyyy.

In [Microsoft Excel], the default date format is visible on the Format cells.

Steps to view the default date format in Excel -

1. Right-click anywhere in the Excel document. A list of options appears on the screen. Click on the Format Cells option.

2. Click on the Date under Category. The date format marked with an asterisk (*) is the default Microsoft Excel date format, as shown in the screenshot below.

Methods to change the Date Format

Follow the below-given methods to change the Date Format in Excel document -

Method 1: Change the Date Format using the Format Cells dialog box

Step 1: Open a new or an existing Microsoft Excel document.

Step 2: Select or Highlight the cell s whose Date format you want to change. You can also select an empty cell to insert the Date.

Note: In our case, we insert date in Cell A1.

Step 3: Press the Ctrl + 1 keys (for Windows) or Control +1 or Command + 1 (for Mac) from the keyboard. A Format Cells dialog box will appear on the screen in which do the following -

1. Click the Number tab .
2. Click Date under the Category list.
3. Select your desired date format under the Type: section. In the Sample box, your selected date will display.
4. Click on the OK button at the bottom of the Format Cells dialog box, as shown in the below screenshot.

Note 1: In our case, we select the 14 March 2001 Date format.

Note 2: If you want to use a date format according to another language that displays dates, select the language in the Locale (location) drop-down menu and select your desired location.

The screenshot below shows that the default Microsoft Excel date format changed to your desired date format.

Note: Using the above steps, if you are still unable to change the Date format, then make the following changes in your excel document.

Step 1: Select a cell.

Step 2: Go to the Data tab on the Ribbon. Click on the Text to Columns button in the Data Tools section.

Step 3: A Convert Text to Columns Wizard appears on the screen with the selected Delimited radio button. Click on the Next button.

Step 4: Click on the Next button to Convert Text to Columns Wizard - Step 3 of 3.

Step 5: Click on the drop-down associated with Date under the Column date format section, select any date format from the Date drop-down menu, and click on the Finish button at the bottom of the Convert Text to Columns Wizard.

Note: In our case, we select DMY format.

Now, follow the steps mentioned in the Change the Date Format using Format Cells dialog box to change the date format.

Method 2: Create a custom date format in Excel

Microsoft Excel provides the easiest way to create a custom date format in an Excel document. It allows us to display days name (like Sun, Mon) using “ddd” and full days name (like Sunday, Monday) using “dddd”. Similarly, it displays short months like (Jan, Feb) using “mmm” and full month name like (January, February) using “mmmm”.

List of Excel Date Formatting codes

Days Code

• d - It is used for the date 1-31
• dd - It is used for the date 01-031
• ddd - It is used for short days names like Sun, Mon, Tue
• dddd - It is used for full days name like Sunday, Monday, Tuesday

Month Codes

• m - m is used to denote months as 1-12
• mm - mm is used to denote months 01-12
• mmm - mmm is used to demote months as Jan, Feb, Mar,…,Dec
• mmmm - mmmm is used to denote months as January, February, March,…,December
• mmmmm - mmmmm is denotes as the first letter of the month.

Year Codes

• yy - yy is used to denote year as 00-99
• yyyy - yyyy is used to denote years as 2000, 2002, 2010, 2012

Steps to create custom Date Format in Excel

Custom date format is the easiest and quickest method to create as well as change a date format. It is also known as suffixing numbers with text strings.

There are the following steps to create a custom date format in Excel -

Step 1: Select the cell where you want to create a custom Date Format.

Step 2: Right-click on the selected cell and click on the Format cells option from the menu or you can also press Ctrl + 1 keys from the keyboard to open the Format Cells dialog box.

Step 3: In the Category section, click on the Custom tab. Remove General by pressing the backspace. Enter your desired date format under the Type : section and click on the OK button at the bottom of the dialog box.

The below screenshot shows that date format dd/mm/yyyy (12/01/2021) convert into the dd.mmm.yyyy (12.Jan.2021) date format.

Create a custom date with day

Microsoft Excel also allows us to create a date with the day. Below are the steps that you need to follow to create a custom date with a day.

Step 1: Select the cell in which you want to create the Date format.

Step 2: Press Ctrl + 1 key to open the Format cells dialog box.

Step 3: Click the Custom option under the Category section. Remove General under the Type: section, enter format dd/mmmm/yyyy(dddd) to view date in the 12/January/2021(Tuesday) Format. Click on the OK button at the bottom of the Format Cells dialog box.

Note: In our case, we want to change 12.01.2021 date format.

The screenshot below shows that the 12.01.2021 date format changed to the 12/January/2021(Tuesday) date format.

Note: Using the above steps, you can also convert MM/DD/YYYY (10/12/2018) date format into DD/MM/YYYY (12/10/2018) date format.