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:
Tuesday, September 24, 2019
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.
[$-en-US]dddd, mmmm dd, yyyy
[$-409]dddd, mmmm dd, yyyy
Below, there is a table with a few examples.
|Locale||Short string||Hexadecimal value|
|English (United Kingdom)||en-GB||0x809|
You can find the full list under the following address.