23. Excel How to change the date format ?
Changing the date format using the Cell Format dialog box
Enter a date value in a cell, for example "01/07/2021".
Select this cell and open the Format button menu in the Cells group on the Home tab of the Ribbon.
Click the Format Cells... button. The Format Cells dialog box is opened.
Click on the Number tab, and then either Date or Time from the Category list.
Choose the format you want from the Type list.
If you can't find the format you want, or if you want the value to be displayed in another language, then choose another value from the Locale (location) list and then look for the format in the Type list.
Custom cell format
In the Format Cells dialog box, click the Number tab, then click Custom from the Category list.
In the Type field, there is a list of format codes. If you click on a code from this list, then it is copied to the top and you can edit it afterwards. In the extract below, I have selected the code :
[$-en-GB]dd mmmm yyyy
Then I changed en-GB to de-DE for German-Germany :
[$-de-DE]dd mmmm yyyy
The value 01/07/2021 displayed as :
01 Juli 2021
The meaning of the date codes in Excel is as follows:
| Code | Meaning | Result for the example 02/07/2021 |
| d | Day of the month in numbers from 1 to 31 | 2 |
| dd | Day of the month in numbers from 01 to 31 | 02 |
| ddd | Day of the week in abbreviated form | Fri |
| dddd | Day of the week in letters | Friday |
| m | Months in numbers from 1 to 12 | 7 |
| mm | Months in numbers from 01 to 12 | 07 |
| mmm | Month in abbreviated form | jul |
| mmmm | Month in letters | july |
| yy | Year without the century | 21 |
| yyyy | Year with the century | 2021 |
NB. These codes can be written in upper or lower case. For example, the codes "mmm" and "MMM" are equivalent.
The meaning of the time codes in Excel is as follows:
| Code | Meaning | Result for the example 15:5:8 |
| h | Hour from 0 to 23 | 15 |
| hh | Hour from 00 to 23 | 15 |
| h AM/PM | Hour from 1 to 12 | 3 PM |
| hh AM/PM | Hour from 01 to 12 | 03 PM |
| m | Minutes from 0 to 59 | 5 |
| mm | Minutes from 00 to 59 | 05 |
| s | Seconds from 0 to 59 | 8 |
| ss | Seconds from 00 to 59 | 08 |
NB. Note that there is a confusion for these symbols between months and minutes.
Using the Excel "TEXT" function
The Excel TEXT function allows the formatting of a numeric or date-time value. It takes as argument a numeric or date-time value and a formatting code and returns the formatted value according to the given code. The codes are the same as those for Custom Cell Format described above.
In the following example the TEXT function returned today's date according to the given formatting. The returned text is concatenated with the text "Today is ".
