Date Format is one of the most important techniques you must know in Excel
- A date is a number
So like any number in Excel, you can apply a specific format
- Customize any part of a date
If you want to display Months in letters, this is possible. You simply have to create your custom date code.
Let's see how in this article
Dates are Integer Numbers
To understand what is a date, the best is to write a number in a cell and change the format
Usually, when you insert a date in a cell it is displayed in the format dd/mm/yyyy or mm/dd/yyyy (for USA).
- Let's say you have the date 02/04/2023 in a cell.
- If you change the cell's format to General, the cell displays 45018 😕🤔
In Excel, a date is the number of days since 01/01/1900 (the first date in Excel). So, 45018 is the number of days between 01/01/1900 and 02/04/2016.
Date format in the ribbon
Dates can be displayed in different ways using the following 2 options (available in the Number Format dropdown in the main menu):
- Short Date
- Long Date
How to customize a date in Excel
A date consists of a day, a month, and a year. Excel allows you to change the display of each of these components by changing the format of the cell.
- d is the code for the day
- m is the code for the month
- y is the code for the year
The trick lies in the number of letters you'll write
- d will write the day as a number, without the 0 for days between 1 and 9
- dd will write the day as a number with always 2 digits, like 01 for the 1st day of the month
- ddd will write the day in the abbreviated form of the day of the week (Mon, Tue, Wed, ...)
- dddd will write the day in full (Monday, Tuesday, Wednesday, ...)
It's the same for the months.
- m to write the digit of the month without a 0 before the number
- mm to write the month with always 2 digits
- mmm to write the month in its abbreviated form (Jan, Feb, ...)
- mmmm to write the month in letter (January, February, ....)
For the years, you can write the value in 2 or 4 digits
- y or yy to write the year in 2 digits
- yyy or yyyy to write the year in 4 digits
- Tip, if you just write the letter e, Excel understands that you want to write the year in 4 digits
Different examples of custom date
The following document is an embedded workbook. You can visualize the date of TODAY in different formats. The code for each date is in column A.
Where to write your custom code?
To customize a date:
- Open the dialog box Custom Number (with the shortcut Ctrl + 1 or by clicking on the menu More number formats at the bottom of the number format dropdown)
- In this dialog box, Go to the Number Tab
- Then select 'Custom' in the Category list
- Write the custom code of your date in the textbox Type.
In function of your language, the letter could be different:
- t for "tag" (day) in German
- j for "jour" (day) in French
- a for "año" (year) in Spanish
Don't write text in your cell !!!
With dates, one of the most common mistakes is to write text inside the format code (1 January 2016 for example). Never do this in Excel. If you do this, the contents of the cell will be Text and not a number
- In Excel, text is always displayed on the left of a cell.
- A number or a date is displayed on the right.
These articles could be useful to avoid this situation.
- How to display Months in Letters and keep the date type in Excel?
- Convert a Text Date to a Real Date (number)