How to make short date in excel

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.

Table of Contents Show

What are Long Date and Short Date Formats?
Method 1: Using the Number Format Menu to Apply Short Date Format in Excel
Method 2: Using the Format Cells Dialog Box to Apply Short Date Format in Excel
Method 3: Using the TEXT Function to Apply Short Date Format in Excel
How do you shorten dates?
How do I convert text to a short date in Excel?
How do you convert a long date to a short date?
How do you convert a number to a short date?

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?
Three ways to Apply Short Date Format in Excel
- Method 1: Using the Number Format Menu to Apply Short Date Format in Excel
- Method 2: Using the Format Cells Dialog Box to Apply Short Date Format in Excel
- Method 3: Using the TEXT Function to Apply Short Date Format in Excel

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.

Moreover, if you see pound symbols “###” in a cell that contains a date, it is usually because the width of the cell is too narrow to display the full date.

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.

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.

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:

Code	Description	Example
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:

Format	Example
dd-mm-yy	13-01-21
mm/dd/yyyy	01/13/2021
mm-dd-yy	01-13-21
m.d.yy	1.13.21
m/dd/yy	1/13/21

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”:

=TEXT(A2,”m/d/yy”)

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:

 ="Date: "&TEXT(A2,”mm/dd/yy”)

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:

How to Apply Accounting Number Format in Excel
How to Apply Comma Style in Excel (3 Easy Ways)
Why are Dates Shown as Hashtags in Excel? Easy Fix!
How to Convert Date to Day of Week in Excel (3 Easy Ways)
How to Convert Date to Month and Year in Excel (3 Easy Ways)
How to Sort by Date in Excel (Single Column & Multiple Columns)
How to Change Date and Time to Date in Excel
How to Calculate the Number of Months Between Two Dates in Excel?

How do you shorten dates?

1. In most countries of the world, dates are written in order DAY/MONTH/YEAR, e.g., 4 July 1999, abbreviated 4/7/1999. 2. In eastern Asia, however, dates are written in order YEAR/MONTH/DAY: 1999 July 4, abbreviated 1999/7/4.

How do I convert text to a short date in Excel?

Convert text dates by using the DATEVALUE function.

Enter =DATEVALUE(.

Click the cell that contains the text-formatted date that you want to convert..

Enter ).

Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date. What is an Excel serial number?.

How do you convert a long date to a short date?

How to quickly apply default date and time formatting in Excel.

Select the dates you want to format..

On the Home tab, in the Number group, click the little arrow next to the Number Format box, and select the desired format - short date, long date or time..