Excel Date Format (Table of Contents)
Date Format in Excel
A date is one of the data types that are available in excel, which we use mostly in our day to day excel data works. A date can be displayed in several 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, Excel comes with multiple numbers of formats to display dates.
How Excel Stores Dates?
Before getting into the data formats, try to understand how Excel stores dates. Excel stores the date in an 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 the 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 an integer value, as shown in the below picture.
Now we will understand what that number is and what calculation is used by Excel to convert the data into an integer. Excel gives the number series for the dates starting from 1 Jan 1900 to 31 Dec 9999, which 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.
I hope you understand how Excel stores the date.
How to Change Date Format in Excel?
Let’s understand how to change the date format in Excel by using some examples.
Example #1
First, we will see a short date and a Long date. Then, we will find the formats Short date and Long date from the same drop list of numbers.
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. Then, select the date and right-click.
The above pop up will appear; from that pop-up menu, select the Format Cells. Then, another screen will appear, which is the “Format cells” screen in which we can apply different kinds of formats like Number, Alignment, Font, Border, fill and protection.
Select Number and select the Date from the Category box. When we select the “Date”, the 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 a * (asterisk) mark, which shows that those are default date formats.
If we want to change the default date settings, we should go to the 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 and 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 a preview of how it will display in excel under the Sample box. There are different formats available like M/D, M/D/Y, MM/DD/YY etc.
Select the M-D format, as shown below.
Then the date will look like 2-11 if we observe in the formula bar, which is highlighted. The formula bar shows 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 dropdown. Observe the below image how the drop-down looks like.
When we select a particular location from the drop-down, then different data 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 the 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 the “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 excel.
Example #5
Date format in Other languages
We can display dates in other languages too. We will be able to do this using the “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 before 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 the German language similarly; we can give date formats for Chinese, Spanish, Japanese, French, Italian, 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 number under the “Home” tab.
- To convert the data 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 the Date Format in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
