Excel Date Format (Table of Contents)
Date Format in excel
A date is one of the data types that is available in excel which we use mostly in our day to day excel data works. A date can be displayed in a number of ways in excel as per requirement. A date has multiple numbers of formats based on geographical regions. Because different geographical regions use a date in different ways. Based on that, Excel comes with multiple numbers of formats to display date.
How Excel stores Dates?
Before getting into the date formats try to understand how Excel stores dates. Excel stores the date in integer format. To make you understand better we will look at the following example. Consider today’s date 11 Feb 2019.
If we observe the above date, it is in the format of Month-Day-Year. Select this date and convert this to general or number format then we will find a number. Let’s see how to convert. Select the date and choose the drop-down list from the Number segment under Home tab. From the drop-down select the option General and observe how the date will be converted.
Once you convert it will change as integer value as shown in the below picture.
Now we will understand what is that number and what calculation is used by Excel to convert the date into an integer. Excel gives the number series for the dates starting from 1 Jan 1900 to 31 Dec 9999 that means 1 Jan 1900 will store as 1 and 2 Jan 1900 will store as 2. Now just try to check for the date 2 Jan 1900.
When we select the general option, it converted to 2 as shown below.
Hope you understand how Excel stores the date.
How to Change Date Format in Excel?
Let’s understand how to change date format in Excel by using some examples.
Example #1
First, we will see a short date and Long date. From the same drop list of number, we will find the formats Short date and Long date.
Short date: As the name itself speaks how it looks like. It will display a date in a simple way that is 2/11/2019. We can observe in the drop down itself how it will display.
Long date: It will display a date in the long format. We can observe in the below image how it will display.
Example #2
A system has one default format to display the date whenever the user inputs the date. We will see how to check the default format in excel. Select the date and right click.
The above pop up will appear, from that pop-up menu select the Format Cells. Another screen will appear which is “Format cells” screen in which we can apply different kind of formats like Number, Alignment, Font, Border, fill and protection.
Select Number and select the Date from Category box. When we select the “Date” right side box will show the different formats available for the different locations.
If we observe the first two date formats which are highlighted in the red box have * (asterisk) mark which shows that those are default date formats.
If we want to change the default date settings, we should go to control panel and select Region and Language then select Formats and change the date format as per your requirement.
Example #3
If we observe the below screenshot, in addition to the dates with ‘ * ‘ there are also different formats. We can select the required date format to change the current date format.
When we select the required format, we can observe preview how it will display in excel under Sample box. There are different formats available like M/D, M/D/Y, MM/DD/YY etc..
Select M-D format as shown below.
Then the date will look like 2-11. If we observe in the formula bar which is highlighted. In the formula bar it is showing as 2-11-2019 however in Excel it is displaying as 2-11.
We can also change the location by selecting the required location from the selected drop down. Observe the below image how drop-down looks like.
When we select the particular location from the drop down then different date formats will appear in the box “Type”. Currently, it is English (United States), now select the English (United Kingdom). Click OK. Then the date formats in the Type will change. Observe the below screenshot.
Observe the formats in the location United Kingdom are different from United States. Still, if you are not happy with the date formats. We can create our customized format.
Example #4
To create a customized format, select the Custom option from the Category box.
Once we select the Custom category we can input the required format in Type. To make it more clear, suppose we want the format like DD:MMMM: YYYY then type this format in “Type” box.
Observe the above image whatever input is in Type the same format is showing in Sample. If we click OK then it will apply to the date in the excel.
Example #5
Date format in Other languages
We can display dates in other languages too. We will be able to do this using “custom” format under the category. For doing this we should know that particular language code. Once we know that code we just need to add the country code prior to our date format.
The language code should be in square brackets preceded with $ followed by a “- “in the format of [$-xxx].
In the below example we created the date format of German language similarly we can give date formats for Chinese, Spanish, Japanese, French, Italian and Greek etc. Use Google for language codes as per your requirement.
Things to Remember
- Dates before 1 Jan 1900 cannot convert to text in excel as it will not read negative numbers hence it remains in the same format.
- Dates can display in short and long formats from the drop down of number under “Home” tab.
- To convert the date into number format with the formula “Date value”.
- Default date represents with “*” symbol, to change the default selection need to go to “Control panel”.
- CTRL + 1 is the shortcut for the “Format cell”. CTRL + ; is used to display the current date.
Recommended Articles
This has been a guide to Date Format in Excel. Here we discussed How to change Date Format in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
