Short Date Format in Excel – 3 Different Methods
(Note: This guide on how to apply a short date in Excel is suitable for all Excel versions including Office 365)
Excel supports short dates, long dates, and customized dates besides numbers. Date and time are the most common data types used in Excel. The default format to display the date is normally dd-mm-yyyy and the same date can be displayed in a variety of ways based on the requirement.
You’ll Learn:
- Different Formats of Displaying Dates
- Short Date Format in Excel
- Long and Short Date
- Different Methods to Apply Short Date in Excel
- Other Formatting
The different formats of dates supported in Excel are shown in the image below.
There are different ways to format the dates and it is important to know all the available formats. Internally, Excel stores the date in the same format regardless of the format you have used to display the date.
The date usually contains the month and the year. Apart from these details, the day of the week and the time can also be included. An example of a long date format is dd-mm-yyyy hh: mm.
And, an example of a short date format is dd-mm-yyyy which is commonly used in date formatting.
Related Reads:
- How to use EDATE in Excel? 11 Easy Examples
- How to Enable Excel Dark Mode? 2 Easy Methods
- How to Graph a Function in Excel? 2 Easy Ways
Different Formats of Displaying Dates
A date can be displayed in a variety of ways as you can see in the example below.
Generally, a date has three components, the date, month, and year. Apart from this, it can include the day of the week and also the current time. In the above example, the date 19-May-2022 is displayed in various formats. But, cell B6 displays the same date as 44700 as the cell has not been formatted to contain the date values.
You should change the format of the cell to accept a date value as shown in the above screenshot.
Excel uses the default date system where the date argument must represent a date between January 1, 1900, and December 31, 9999. Excel stores date as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900, is stored as serial number 1 and May 19, 2022, is serial number 44700 because it is 44700 days after January 1, 1900.
Short Date Format in Excel
As mentioned earlier, Excel stores the date in the dd-mm-yyyy format by default no matter what formatting you choose.
In the example above, cell A5 displays the date in a long format as Friday, 17 July 1987. In the formula bar, the date is displayed in the default date format as 17-07-1987.
Similarly, another example of a short date format is displayed in cell B5 as yyyy-mm-dd. Excel displays the same date as 17-07-1987, which is the default format.
Long and Short Date
The Long Date format contains the day of the week along with the date, month, and year details. It might not be the most convenient format as a long date format would require a longer column width to accommodate its length.
In the example above, the long date format is selected for displaying the date. Instead of displaying the date in the desired format, you see a series of ### (hash) symbols in the cell that contains it. The reason is that the width of the column is too narrow to display the full date.
When the date is in a long format, the column size has to be increased to accommodate the long date. Instead of increasing the column width, the date can be shortened using a short date format.
In the example above, the column width is increased to accommodate the long date format that contains the week of the date along with the date information.
Also Read:
- How to Add Leading Zeros in Excel? 4 Easy Methods
- How to Use Nested VLOOKUP in Excel? A Step-by-Step Guide
- Excel String Compare – 5 Easy Methods
Different Methods to Apply Short Date in Excel
There are different methods to apply a short date format in Excel. Let’s see each of these with examples.
Method 1: From Home Tab
Follow these steps to apply the short date format to the given date. In the Excel spreadsheet, select the list that contains the dates to format. Click on the Home tab, and then click the Number category.
You can see various formats available in the drop-down menu to choose from, such as :
- General
- Number
- Currency
- Short date
- Long date
- Time
- Percentage
- Fraction
- Scientific
- Text
Choose Short Date from the menu and you can see the table containing the dates change to short date in the format dd-mm-yyyy. The below table shows all the dates in Short Date format in Excel.
Method 2: Using the Format Cell
You can apply the Short Date format using the Format Cells by selecting the date that you want to format and right-clicking on it. This is a much more versatile method as you can customize the date components by separating the date, month, and year with hyphens (-), slashes (/), or dots (.).
To apply the Short Date format, select the range of cells that contains the dates to format, then right-click on it. The keyboard shortcut to get this menu is to press CTRL+1 on the keyboard. You will get the drop-down menu as seen below.
Click on the Format Cell and choose Date from the various categories. By default, the category General is set for all the cells.
When you click on Date, there are many types of date formats and you can choose one for your date.
Method 3: Custom Cells
You can change the date format through the Custom option in the drop-down menu. There are a variety of options to choose from, but choose the format that you want the date to be displayed. In the example below, a short date displaying only the date and month is displayed.
Select the list of cells that contains the date and then select the format dd-mm-yyyy. The sample box will provide a preview of the date based on the type selected.
If you select the format as mm-dd-yy, then the date will be formatted to display two-digit month, day, and year separated by hyphens as shown below.
Using a custom format, you can display a month number without a leading zero by using a single month (m) in the format list.
Other Formatting
Dates can be customized to set the regional settings depending on the location you reside. Dates are represented differently in each location and you can convert the date format to another locale by choosing Locale (location) and selecting a country from the drop-down menu.
In the example below, the language under the Locale (location) drop-down menu is chosen as English (U.S.)
Suggested Reads:
- How to Hide and Unhide Columns in Excel? (3 Easy Steps)
- How to Stop Excel From Rounding? 4 Easy Ways
- How to Make a Pie Chart in Excel? A Step by Step Guide
Final Thoughts
In this guide, we explained how to apply the short date format in Excel. Besides numbers, dates are used extensively in Excel, and practicing them would benefit you when you use financial data in spreadsheets.
Please visit our free resources center to know more about the date and time functions.
We have high-quality Excel guides and you will be really excited to know how handy and useful these functions are.
Simon Sez IT has been teaching Excel for over ten years. For a low monthly fee, you can access 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
