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.

Excel 2016 Date Cell Format

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

Excel 2016 Custom Cell Format

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

Excel 2016 Example TEXT Function

<< 22. Excel How to calculate a percentage?

24. Excel How to delete every other row ? >>

Reader comments

Add a comment