How to Apply Short Date Format in Excel?

by

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:

Date in long form format

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. 

Also read: How to Insert Date in Excel?

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:

Dataset with dates in different formats

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:

  1. Select the cell or range of cells to which you want to apply the Short Date format.
  2. Click the Home tab from the main menu.
  3. Click on the dropdown arrow in the “Number” group (shown in the image below):
Click home and then the date format drop down
  1. In the dropdown menu that appears, you should see a number of number formatting options. Click on the ‘Short Date’ option.
Select short date from drop down

You should now have the Short Date format applied to all the selected cells as shown below:

Dates converted to short format

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)

Also read: How to Remove Year from Date in Excel?

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:

  1. Select the cell or range of cells to which you want to apply the Short Date format
  2. 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.
Click on Format cells
  1. This will open the Format Cells dialog box. Select the Number tab.
Click on Number tab
  1. Make sure that the ‘Date’ option is selected from the list under ‘Category’.
Click on the Date option
  1. This will display a number of date formatting options on the right side of the dialog box.
  2. 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:
Select the date in short format
  1. 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:

Date converted to short format

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). 

Customizing the short date format

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:

Date in mm-dd-yy format

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:

CodeDescriptionExample
mMonth number without leading zero1
mmMonth number with leading zero01
dDay number without leading zero5
ddDay number with leading zero05
yyYear with last 2 digits only21
yyyyFull-year with 4 digits2021

You can combine the above codes to create your required short-date format.

Here are a few example date formats that are commonly used:

FormatExample
dd-mm-yy13-01-21
mm/dd/yyyy01/13/2021
mm-dd-yy01-13-21
m.d.yy1.13.21
m/dd/yy1/13/21
Also read: How to Apply Long Date Format in Excel?

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:

TEXT formula to show date in mdyy format

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment