How to Apply Short Date Format in Excel

Short dates are the best date formats to perform calculations in Excel. Since the long dates have days and sometimes even timestamps, subtracting these dates would result in ###### error. In such situations, you should convert the long dates into a short date format. 

Apart from calculations, short-date formats are also perfect to shrink the column space. For Instance, if you simply enter 3-15 as a Date, you can save room for other columns and fit them into one page.

Nonetheless, knowing how to apply short-date formats can be extremely helpful if you use Excel on a daily basis. For this, we have compiled six methods in this article. You could use the pre-built short-date format or create your own.

Use Shortcut Key

Firstly, you can use Excel’s shortcut key to change your long date into the short date format. Compared to other methods, it is the fastest way as you just need to press down the keyboard shortcuts. For this, select the dates and enter the following keys together to apply the default short date.

Shortcut key: Ctrl + Shift + #

From Number Section

While the shortcut method is convenient, it has limited option when it comes to date format. If you do not like the short-date format you got with the shortcut key, use this method.

The Number section is used to convert numbers into any special formats like long/short dates, Currency, Accounting, and many more.

Simply, select the dates and go to Home Tab. Then, on the Number section, expand the drop-down menu and pick Short Date.

From Format Cells

In the Number section, you can find only one default system short date format to apply. So, for users who want to explore more, you can opt for the Format Cells Window. Here, you can find a variety of pre-built short-date formats to choose from. 

1. Select cell ranges with the date and enter Ctrl + 1.
2. On the Format Cells window, click Number Tab and go to Date category.
   
3. Under Type, scroll down the date formats to see all. Once you find the short date, select it and click OK.
   

Create a Custom Short Date Format

If you didn’t find the relevant short-date format in the Date Category, you can also make your own custom short-date format and apply them.

Before you begin, here are list of custom codes you could add to make the short date format. You can take them as a reference while creating a custom format.

1. Select dates and press Ctrl + 1.
2. Click the Number Tab on the Format Cells window. Then, head to Custom category.
   
3. Under Type, enter short date format. Here, we typed in d-mm format.
   
4. Click OK.

Using TEXT Function

TEXT function returns the numbers in the format you apply to. I recommend you use this function if your long date has timestamps in it. Also, this method is best if you do not want to directly apply the short-date format to your data.

Syntax: TEXT(Cell Reference, Format)

In the given case, to convert the 2/9/2023 14:11 date of cell B2, we entered the following formula in a new cell. Then, using the flash-fill handle, we filled other cells too.

=TEXT(B2, “d/m/yy”) 

Change System Date Format

Excel records the short date as per your PC’s default system format. So, if you find yourself constantly changing your date to a specific format, you can change the system date format itself. 

1. On your PC, press the Windows + R keys for the Run command.
2. In Open field, type in intl.cpl and click OK.
   
3. Under Format, pick your Country.
   
4. Now, on Short date, expand the Drop-down list and choose your format.
   
5. Click OK.