In Excel, you can display the same date in many different ways just by changing the date format
Table of Contents
What is a date in Excel?
Dates are whole numbers
Usually, when you insert a date in a cell, you have this result
Now, if you change the cell's format to Standard, the cell displays 42370
Explanation of the numbering
In Excel, a date is the gap in number of days between the 01/01/1900 (which is the first date in Excel) and your date.
So 42370 is the number of days between the 01/01/1900 and 01/01/2016.
When you have a date in a cell, you can display it in different ways using the following options.
In the ribbon, you have 2 options
- Short Date
- Long Date
Customize your date format
You can write the same date with different format like this
- Jan 2018
- Mon, 1 Jan 2018
Parameters of the dates
A date has 3 parameters
- d for the day
- m for the month
- y for the year
In function of your local setting, the letter could be different:
- t for "tag" in German
- j for "jour" in French
How to customize a date?
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 list of formats)
- In this dialog box, you select in the right-hand side 'Custom' and in 'Type' you write the code of your date.
Now, you just have to write the parameter d, m or y different number of times. By entering the codes 1,2,3 or 4 times, the display will be different.
Don't write text in your date ⚠
With dates, one of the most common mistakes is to write text inside the date (1 January 2016 for example) Never do this in Excel ⛔⛔⛔
If you do this, the contain of the cell is a Text and not a number
- In Excel, text is always on the left of a cell
- A number or a date is on the right
All the display in function of the different code
The following document shows you the result on the date in function of the code
Different examples of custom date
Here, you have different examples of display with the same date.