Working with dates in Excel might not be as simple as working with plain numbers and text, and the reason is that Excel’s way of storing and displaying dates is very different.
Moreover, Excel can display the same date in multiple ways (or formats).
The good news though is that it is possible to convert between date formats quite easily.
In this tutorial we will show you three ways to apply the Short Date format to any date in Excel:
- Using the Number Format menu
- Using the Format Cells dialog box
- Using the TEXT function
What are Long Date and Short Date Formats?
One of the formats in which Excel displays dates is the Long date Format.
This format displays the day of the week along with the full month name as shown below:
Notice the actual date in the formula bar is just 1/1/2021.
However, since the cell is formatted to display the date in Long format, it shows the date in the cell as Friday, January 1, 2021.
The Long Date format might not be the most convenient form to display your date.
It’s too long, so it ends up taking a lot of space and you would need to increase the column width to accommodate the full format.
This often happens when your cell contains the date in Long format.
A good way to reduce the column size or to handle “###” errors would be to apply the Short Date format to the cells.
Three ways to Apply Short Date Format in Excel
Let us look at three ways in which we can apply the Short Date format to a given cell or range of cells in Excel.
We will apply each method to the set of dates shown below:
Notice that each date is displayed in a different format.
For example, the date in the first row is in Long Date format, while the one in the second row is displayed in the form of the serial number that represents the date.
We are going to apply the Short Date format to all the above dates by the following three methods.
Method 1: Using the Number Format Menu to Apply Short Date Format in Excel
The first method is actually the quickest way to apply the Short Date format to a cell or range of cells.
All you need to do is:
- Select the cell or range of cells to which you want to apply the Short Date format.
- Click the Home tab from the main menu.
- Click on the dropdown arrow in the “Number” group (shown in the image below):
- In the dropdown menu that appears, you should see a number of number formatting options. Click on the ‘Short Date’ option.
You should now have the Short Date format applied to all the selected cells as shown below:
In case you don’t see the cell content convert into the short date format, a possible reason could be that the cell has text content (which is not something that Excel considers a date)
Method 2: Using the Format Cells Dialog Box to Apply Short Date Format in Excel
This method is a little more versatile as it also lets you customize your Short date according to your requirement.
For example, you can set the components of the date to be separated by hyphens (-), slashes (/) or dots (.).
Here are the steps that you need to follow to apply Short Date format using this method:
- Select the cell or range of cells to which you want to apply the Short Date format
- Right-click on your selection and select ‘Format Cells’ from the context menu that appears. Alternatively, you can use the CTRL+1 shortcut from your keyboard.
- This will open the Format Cells dialog box. Select the Number tab.
- Make sure that the ‘Date’ option is selected from the list under ‘Category’.
- This will display a number of date formatting options on the right side of the dialog box.
- Select the short date format that you want to apply to your dates. For a general Short Date format, you can simply select the option shown below:
- Click OK to close the Format Cells dialog box.
You should now have the Short Date format applied to your selected cells as shown below:
If you need a more customized date format, then you can type in the format that you want in the input box under ‘Type’, after selecting the ‘Custom’ option from the ‘Category’ list (instead of step 4).
For example, if you type the format “mm-dd-yy” then your date will be formatted to display two-digit month, day, and year numbers separated by hyphens, as shown below:
Your customized format needs to follow certain rules. For example, to represent the month number without a leading zero, you can use the “m” code.
To display the month number with a leading zero, on the other hand, you can use the “mm” code. Here are some of the codes that you can use:
|m||Month number without leading zero||1|
|mm||Month number with leading zero||01|
|d||Day number without leading zero||5|
|dd||Day number with leading zero||05|
|yy||Year with last 2 digits only||21|
|yyyy||Full-year with 4 digits||2021|
You can combine the above codes to create your required short-date format.
Here are a few example date formats that are commonly used:
Method 3: Using the TEXT Function to Apply Short Date Format in Excel
The first two methods apply the Short Date format to the original cell(s).
If, however, you want to keep the original date(s) in the Long format as is and display the Short format in a separate cell, then you can use the TEXT function.
The TEXT function lets you convert dates to different text formats.
It takes a DATE value and lets you format it according to a given format code.
The syntax for the TEXT function is:
= TEXT (date_input, date_format_code)
In the above formula syntax:
- date_input is the date value or a reference to a cell containing a date that you want to apply the Short Date format to.
- date_format_code is the format code that specifies how you want the resulting date to look.
These format codes are the same as the codes discussed in Method 2.
The TEXT function applies this format code to the specified date_input and it returns a text string in the given format.
For example, the following formula takes the date provided in cell A2 and returns the date “1/1/21”:
Note: Always remember to enclose the format_code in quotes.
Insert this formula in cell B2 (of our sample dataset) and copy it down to the rest of the cells in column B.
Here’s how the result that you should get:
The only issue with the TEXT function is that the date that is returned is actually a string and not a DATE value.
That is why you see the results aligned to the left side of the cells instead of the right. So you cannot perform subsequent operations with the result (i.e., you can’t use it in calculations that use date as the input).
The benefit of using the TEXT function to get the date in the short format is that you can add any text string before or after the date.
For example, if you want to show the result as Date: 01/01/2021, you can use the below formula:
In the above formula, I have appended “Date: ” before the TEXT function, which will make sure that the result I get would have the desired text before the date followed by the date in the specified format.
In this tutorial, we showed you three ways in which you can apply the Short Date format in Excel.
The first method is the quickest and the most commonly used. The second method is more versatile as it lets you customize the short date to your required format.
The third method is not used all that often, but you can use this method if you don’t want to make changes directly to the original cells.
We tried to make this tutorial simple and easy to follow.
We hope it was helpful.
Other Excel tutorials you may also like: