To display the months in letters in your dates, you have to create a custom date format.
- Do not write the months directly in letters in the cells
This would result in text in a cell instead of a date
- Create a custom format of your dates to display the months in letters
By creating your own date display code, you'll have your months in letters
Don't make the mistake of writing your Months in Letters yourself
Indeed, by doing this you are not writing a date but a text.
- The contents of the cell are cropped on the left, so it's text
- The formula bar also shows that your cells contain text and not a numeric date.
If you have dates written in text, you can convert them to numeric dates with this trick.
Apply a Custom Date Format
In fact, Excel allows you to write your months in Letters while keeping the Date type as shown in the following image
- The formula bar does display a date
- The contents of the cells are framed on the right, so it's a numeric value
- The months are all written in letters
To do this, we have to customize the date format.
How to construct 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
You can perfectly combine multiple codes. For example, to display the month in number and the months in letters at the same time with the code mm mmmm.
Where to write the code for a date?
To display a date in one of the formats listed below, you will need to write it in the Custom Formats dialog box.
- Either with the keyboard shortcut Ctrl + 1
- Either by selecting the last option of the number format (More Number Formats)
- Select the Number tab
- Then Custom
- In the Type box, enter your date format code