How to Change Date Format in Excel (4 Ways)

Dates are one of the most common data types we use in Excel. Using and interpreting dates can be confusing, as Excel has several formats to display the same date. For this, we need to have a proper understanding of the date formats and how they work.

Date formats are the way dates are displayed in Excel.

In this Excel tutorial, we will discuss 4 ways to change the Excel date format. The ways include: changing the default format using the keyboard shortcut, the number format box from the ribbon, the format cells dialog box, and using the DATEVALUE function. We also discuss changing the default date format from the control panel and changing to different locales to change the date format.

Date Format Codes

A date format code represents how a date is displayed. Changing the code means changing the way to display a date.

There are dedicated codes in Excel for how we want to display days, months, and years. These are shown below:

Code for Days:

Code for Months:

Note: Using mmmmm can get confusing as we have different months with the same first letter. For example: all three of January, June, and July will display the date as J. August and April both display A.

Code for Years:

4 Ways to Change Date Format in Excel

After inserting a date in Excel, we may need to change the format for various reasons. We can change the date format in three different ways: using the keyboard shortcut, the number format box, the format cells dialog box, and using the DATEVALUE function to format dates stored as texts.

1. Using Keyboard Shortcut

To apply the default format, use the keyboard shortcut:

1. Select the cell or range of cells.
2. Press Ctrl + Shift + 3(#) on your keyboard.

The keyboard shortcut applies the short date formatting to the date.

2. Using Number Format Box

The number format box can be accessed from the ribbon of the Home tab. This allows us to format the date as short or long.

To format the date using the number format box:

1. Select the cell or range of cells.
2. Go to Home > Number group > Number Format box.
3. Select Short Date or Long Date.
   The date will be displayed accordingly.

3. Using Format Cells Dialog Box

If you want more options to display the date, you can use the Format Cells dialog box. This can be used to make a custom date format too.

To format the date using the format cells dialog box:

1. Select the cell or range of cells.
2. Press Ctrl + 1 on your keyboard.This brings out the Format Cells dialog box. In the Format Cells dialog box:
3. Go to the Number tab and select Date in the category field.
4. Select the preferred format from the Type field.
5. Click on OK.

The date will be displayed according to your selected format.

Creating a Custom Date Format

If you do not like the options provided in the Type field in the Format Cells dialog box, you can add a custom format for the dates.

To add a custom format:

1. Select Custom in the Category field.
2. Insert the custom format in the Type box.
3. Click on OK to close the Format Cells dialog box.

The custom format will be applied to the selected cells.

4. Using DATEVALUE Function to Change Date Stored as Text

Sometimes, when we import data from elsewhere, the dates might be displayed as texts in Excel. This creates a problem when we want to change the formatting. To solve this, we can use the DATEVALUE function.

We have to remember that in Excel, dates are stored as serial numbers. January 1st, 1900 is given a serial number of 1, January 2nd, 1900 is denoted by 2, and so on. Every date is assigned a unique serial number.

The DATEVALUE function returns the serial number of a particular date stored as text. This serial number can then be converted to dates and we can apply formatting to it.

To change the formatting of dates stored as text using the DATEVALUE function:

1. Go to cell E4.
2. Insert the following formula: =DATEVALUE(D4)
   Now we can apply any formatting we like to the value.
   
3. Press Ctrl + Shift + 3(#) on your keyboard to apply the default date formatting to the cell.

How to Change Default Date Format

The default date format is the one that we see after inserting a date in Excel. The default date format is determined by the native settings of our computer. There are two default date formats: Short Date and Long Date. We can easily notice them in the Format Cells dialog box as they are marked with an asterisk(*).

The default date format can be changed from the Control Panel of Windows.

Follow these steps to change the default date format:

1. Go to the Control Panel.
2. Select Change date, time, or number formats under the Clock and Region section.A new window named Region appears. The default location based on which the date is displayed can be changed in the Format section.
3. Select the format from the Short date and Long date drop-downs.
4. Click on Apply and then OK.

Applying Custom Format for Default Date Format

Now what if you want to apply a custom formatting to the default date format? We can choose from a limited number of options in the drop-down menus. But, we can easily insert a custom date format that we like and easily understand.

Follow the steps below:

1. Click on Additional settings in the Region window.
2. Go to the Date tab.
3. Insert custom formats in the Short date or Long date boxes.
4. Click on Apply and then OK to apply the formatting.

How to Change Date Format to Other Locale

Every country handle dates a little differently.  Each country has its unique style when it comes to the way of displaying dates. Thats why, selecting a certain region can display the date according to that region.

We can select a location to view a date according to that location from the Number tab of the Format Cells dialog box.

Follow the steps shown below:

1. Select a language from the Locale(Location) drop-down menu.
   This will modify the date formats in the Type field accordingly.
2. Choose the preferred format.
3. Click on OK to apply the format.

The selected format will be applied to the selected cells.

Custom Date Format for Other Locales

Now, what if you don’t want to change the location but still want to display a date according to a different language? In that case, you have to use the corresponding language code associated with a language. The code will allow you to display the date in that particular language. The code has to be used inside square brackets ([]) followed by a dollar sign ($) and a dash (–) in the Custom tab of the Format Cells dialog box.

The German language is denoted by code 407. If you want to display a date in German, you have to use the code: [$-407]

Some of the most popular languages and their codes are:

The steps to do this are described below:

1. Insert the language code in the Type box along with the format.
2. Click on OK to apply the formatting.

The format for the German language is applied to the selected dates.

Things to Remember

1. If, after changing the format, the date is displayed as #####, the cell is probably not wide enough to show the full date. Modify the cell width to solve this issue.
2. The TEXT function can be used to convert a date into text.

Download Practice Workbook

Conclusion

In this article, we have described the way to change the date format from the control panel and change the date format in Excel.

We have also shown the process of changing the location to change the date format according to that location.

We recommend you select a date format according to the requirements of your dataset and the one you understand easily.

We hope that this article clears up all your confusion regarding the different date formats.

Excel Change Date Format: Knowledge Hub

<< Go Back to Date Format | Number Format | Learn Excel