Apply the Short Date Format in ExcelLearn how to apply the short date format to date values in Excel
Dates in Excel are pretty weird.
While they can function as numbers and be treated as such (date is a number format after all), they aren’t exactly “regular” numbers.
I mean, you just can’t add up 01/01/2022 and 01/01/2023 right?
That doesn’t make sense.
Yet, the original data of the dates must only contain numeric values.
There are various ways Excel can display a date.
It could be in a long date format where the month is completely spelled out (e.g. February, March, April), as well as the day and the year.
Sometimes, it could also include the time.
Excel could also display a date in a short date format.
For example, the month only comes in three letters (e.g. Feb, Mar, Apr).
Or the month could be represented by numbers (such as January being 1 or 01).
Whichever of these formats you prefer, you would want to know how to apply these different date formats.
This is so that you can apply the most appropriate one when needed.
In this article, you will be learning how to apply the short date format to any date value in Excel.
You’ll be learning three easy methods to do so.
By the end of the article, you should be able to apply the short date format to any date value whenever you need to.
Long Date Format vs Short Date Format
Before we proceed with discussing the different methods of applying the short date format, let’s expound on the differences between the long date and short date formats.
Here’s a visual representation of how the two date formats look like:
As you can see, the long date format completely spells out the month (e.g. January instead of Jan).
It also conveniently shows the weekday (e.g. Monday, Tuesday).
This could be useful if you want to know the day of the date.
However, the long date format takes up a lot of space, which can be inconvenient presentation-wise.
You will most probably have to increase the column width just to accommodate the entire format. (Note: If you see pound signs (#) instead of date values, that’s because the column width isn’t wide enough to accommodate the date)
On the other hand, the short date format presents the date values in a short yet understandable format.
Instead of completely spelling out the month, it is represented by a number instead.
Also, the day of the date doesn’t show. It’s just enough to tell you the important details of the date.
This is convenient if you want your datasheet to be as compact as possible.
In most cases, you probably don’t have to show the day of the date. You probably also don’t have to completely spell out the month.
That’s why you’re most likely to use the short date format more often than the long date format.
Use the Number Format Dropdown Menu to Apply Short Date Format in Excel
For the first method, we will be using the number format dropdown menu. This dropdown menu can be accessed in the home tab.
You should find it somewhere in the middle of the ribbon (it looks like a box).
By default, it will show ‘General’ and will have a down arrow on the right:
To access the dropdown menu, you’ll have to click on the down arrow.
How to Apply Short Date Format
For illustration purposes, we’ll be using the following dataset:
You’ll notice that each date above is presented in a different format. Your goal here is to present the dates in the short date format. And you’ll be doing so by using the number format dropdown menu.
- Select the cells that contain the dates to which you want to apply the short date format.
- Access the number format dropdown menu. To do so, open the home tab. Somewhere in the middle, you should find a box that states the selected cell’s format (or the first cell in the case of a group of cells). By default, this will show ‘General’. There should be a down arrow on the right side of this box. Click on it to access the number format dropdown menu.
- Among the options, select ‘Short Date’ (click on it).
- This will apply the short date format to your selection.
Note that method this won’t work if the cell contains text values. Excel does not recognize text as date.
Use the Format Cells Dialog Box to Apply Short Date Format in Excel
For the next method, we will be using the Format Cells dialog box. It is a very useful and versatile dialog box that lets us perform all formatting functions (for the selected cell/s) in one place. There are two ways to access this dialog box: (1) to right-click on the selection and select the Format Cells option; and (2) press the keyboard shortcut Ctrl + 1.
With the Format Cells dialog box, you can customize your date separators. For example, you can set hyphens (-) instead of slashes (/) to separate the month, day, and year.
How to Apply Short Date Format
For illustration purposes, we’ll be using the following dataset:
Your goal is to present the dates in the short date format. This time, you’ll be doing so by using the Format Cells dialog box.
- Select the cells that contain the dates to which you want to apply the short date format.
- Open the Format cells dialog box. To do so, right-click on your selection. This will present you with a list of options. Select Format Cells from among them. Alternatively, you may press the keyboard shortcut Ctrl + 1.
- In the Format Cells dialog box, open the Number tab. Then under “Category”, select “Date”.
- On the right side of the dialog box, you will see several date format options. Select your preferred short date format then press the OK button.
- The selected short date format should now be applied to your selection.
Customize Date Format
You can also use the Format Cells dialog box to apply a personalized date format. To do so, rather than select “Date” under “Category”, select custom instead.
On the right side of the dialog box, you should see a textbox under “Type”. This is where you’ll be inputting your personalized date format:
After inputting your date format, click the OK button.
For reference, here are some codes that you can use to set your date format:
And here are some custom date formats:
Use the TEXT Function to Apply Short Date Format in Excel
For the last method, we’ll be doing things a bit differently. For the first two methods above, the short date format is directly applied to the original cells.
However, for this method, we will be displaying the short date format in a separate format. And to do so, we’ll be using the TEXT function.
Since we’ll be using the TEXT function, the result will be a text string rather than a date value.
This could be good or bad depending on the situation. But you’ll be able to show to original date format and short date format side by side.
The formula to use the TEXT function is as follows:
= TEXT(date_input,date_format_code)
Where:
date_input – refers to the date value or cell reference (to the cell that contains the date) to which you want to apply the short date format
date_format_code – refers to the format code which will specify how the resulting date will look
How to Apply Short Date Format
For illustration, we will be using mm/dd/yyyy as the short date format. We’ll also be using the following dataset for illustration purposes.
- Select a blank where you will show the resulting short date format. Ideally, it should be adjacent to the cell that contains the date to which you want to apply the short date format. For our illustration, we will be selecting cell B2.
- In the blank cell, enter the formula for the TEXT function. For our illustration, the formula will be =TEXT(A2,“mm/dd/yyyy”). Remember to enclose the format code in quotation marks(“”). Press the Enter key after doing so.
- The selected cell should now show a short date format of the reference cell.
- Copy the formula to the rest of the column (until the next empty row).
Conclusion
And those are three ways to apply the short date format to date value in Excel.
The first two methods directly apply the short date format to selected cell/s.
On the other hand, the third method shows the short date format in a separate cell.
Use the method that best suits your preference and/or needs.