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 ".

---