Change Date Format in Excel

There are many different date formats you can use to display a particular date. You can use short or long formats. Excel can display a date in a number of ways, but it always stores it as a number.

Here, are different date formats representing the same date.

To find out what is the number representation of a particular date, you can format it as a number.

Formatting dates using Format Cells

One of the most popular ways to format a date is to use the Format Cells feature. To use it, right-click a date you want to format and from the contextual menu, choose Format Cells.

A new window will appear. Make sure that you have the Number tab and the Date category selected.

At the top, inside the Sample area, you can see how the date will look like if you apply changes from the Type field.

Below, there is a field called Locale (location), which is the default time set in Windows. In this case, it’s English (United States).

You can change it to a different country from the dropdown menu.

Custom date formats

There are a lot of different date formats inside the Type area. But sometimes you may want to choose the one that is not present there. In this case, you can create your own by clicking the Custom category.

By default, Excel will highlight this format that is currently used. Here, you can edit this format inside the type field, and see a real-time update of the date inside the Sample field.

In the table below, there are formatting codes for dates.

Format	Description	Example (September 24, 2019)
d	Day number without leading zero	24
dd	Day number with leading zero	24
ddd	Day of the week – short	Tue
dddd	Day of the week – long	Tuesday
m	Month number without leading zero	9
mm	Month number with leading zero	09
mmm	Month name – short	Sep
mmmm	Month name – long	September
mmmmm	Month’s first letter	S
y or yy	Year – last two digits	19
yyy or yyyy	Year – last four digits	2019

The TEXT function

The next method you can use to change the date format is by using the TEXT function. It will convert a value to a specific format. This function takes two parameters. The first one is a value you want to use, and the second one is a format in which you want to display this value.

The following example shows a few formats you can use to format date.

Quickly apply a date format

Excel offers a quick way of formatting a date using short date and long date formats. You can find them by navigating to Home >> Number >> Number Format. Expand the dropdown menu to display formatting options. These formats offer quick and easy access to format numbers, text, dates, etc.

For dates, there are two options: short and long dates with a preview.

Click one of them to get the desired formatting.

These formats are based on the default formats of your country. If you want to change it, you have to do it outside Excel.

The default format on your computer

If you work on a PC, your default format is probably the one that is used in your country. Most of them use DMY, and many of them use additionally YMD. The exception is the United States which use MDY.

To check your computer format, you need to open your Control Panel.

Choose View by: Category and click Change date, time, or number formats.

A new window, called Region will appear. Choose the Formats tab.

Under Format, you can choose a language you want to use. By default, the format is set to the language of your Windows installation.

Below, in Date and Time formats, there are two positions we are interested in: Short date and Long date.

If you change the country, these values will be different.

If you want to modify the values you currently have, click the Additional Settings… button to open the Customize Format window, navigate to the Date tab and change the Short and Long dates.

Formatting dates specific to language by TEXT function

So far, you’ve learned how you can change the locale for dates and also how you can do it permanently inside Control Panel.

This time, I’m going to show you how to change locale inside the TEXT function.

Let’s take a look at our example:

There are two ways you can achieve it for the United States:

• The first way is to use short string: en-US
• In the second method is by using the hex value: 0x409

This is how it looks like inside Excel.

or

Below, there is a table with a few examples.

Locale	Short string	Hexadecimal value
Polish	pl	0x15
Polish (Poland)	pl-PL	0x415
English (Germany)	en-DE	0x1000
English (United Kingdom)	en-GB	0x809
German	de	0x7
English	en	0x9

You can find the full list under the following address.